/*
 * Created on Feb 16, 2005
 */
package com.scalar.chiptrack.finance.openpo.dao.local;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.regex.*;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.ClientInfo;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.openpo.*;
import com.scalar.chiptrack.finance.openpo.dao.IPurchaseOrderDAO;
import com.scalar.chiptrack.finance.openpo.dao.PurchaseOrderDAO;
import com.scalar.chiptrack.finance.openpo.forms.OpenPurchaseOrderForm;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderReceiptForm;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderSearchForm;
import com.scalar.chiptrack.finance.openpo.forms.ReceiptSearchForm;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderForm;
import com.scalar.chiptrack.sales.SalesAuditTrail;

import com.scalar.chiptrack.users.dao.UserDAO;
import com.scalar.chiptrack.utils.*;
import com.scalar.chiptrack.utils.SqlQueryMappings;

/**
 * @author Ravikanth
**/
public class LocalPurchaseOrderDAOImpl implements IPurchaseOrderDAO, Constants
{
	public ArrayList loadPOVendors() throws ScalarException
	{
		ArrayList vendorList 	= new ArrayList();
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;
		LabelValueBean	vendor	= null;

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareStatement( "SELECT DISTINCT Vendor.V_Name, Vendor.Vendor_id FROM Vendor, Location WHERE Vendor.Vendor_Id = Location.Vendor_Id AND Location.L_Type IN ( 'Assembly', 'Fab', 'Sort', 'Test' ) AND Vendor.V_Is_Deleted = 'N' Order By Vendor.V_Name " );
			rs 		= pstmt.executeQuery();

			while( rs.next() )
			{
				vendor	= new LabelValueBean( rs.getString( "V_Name" ), rs.getString( "Vendor_id" ) );
				vendorList.add( vendor );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Vendors ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Vendors" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Vendors" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Vendors" );
				}
			}
		}
		return vendorList;
	}

	public ArrayList loadAllVendors() throws ScalarException
	{
		ArrayList vendorList 	= new ArrayList();
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;
		LabelValueBean	vendor	= null;

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareStatement( "SELECT DISTINCT Vendor.V_Name, Vendor.Vendor_id FROM Vendor WHERE Vendor.V_Is_Deleted = 'N' Order By Vendor.V_Name" );
			rs 		= pstmt.executeQuery();

			while( rs.next() )
			{
				vendor	= new LabelValueBean( rs.getString( "V_Name" ), rs.getString( "Vendor_id" ) );
				vendorList.add( vendor );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Vendors ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Vendors" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Vendors" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Vendors" );
				}
			}
		}
		return vendorList;
	}

	public ArrayList searchOpenPO(PurchaseOrderSearchForm purchaseOrderSearchForm) throws ScalarException
	{
		ArrayList purchaseOrderList = new ArrayList();
		Connection conn         = null;
		CallableStatement cstmt = null;
		ResultSet rs            = null;
		PurchaseOrder purchaseOrder = null;
		String poNumber			= "";

		try
		{
            if ( purchaseOrderSearchForm == null )
            {
                purchaseOrderSearchForm = new PurchaseOrderSearchForm();
            }

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			cstmt 	= conn.prepareCall( "{ call sp_search_open_pos( ?, ?, ?, ?, ?, ? ) }" );
			poNumber	= purchaseOrderSearchForm.getPoNumber();
			if( poNumber == null )
			{
				poNumber	= "";
			}
			else
			{
				poNumber = poNumber.trim();
			}
            cstmt.setString(1, poNumber+"%" );
            cstmt.setInt   (2, purchaseOrderSearchForm.getVendor_id() );
            cstmt.setString(3, purchaseOrderSearchForm.getValidFromDate() );
            cstmt.setString(4, purchaseOrderSearchForm.getValidToDate() );
            cstmt.setString(5, purchaseOrderSearchForm.getYear() );
            cstmt.setInt   (6, purchaseOrderSearchForm.isShowExpired() == true ? 1 : 0 );
			rs 		= cstmt.executeQuery();

			while( rs.next() )
			{
				purchaseOrder = new PurchaseOrder();
				purchaseOrder.setPurchaseOrder_Id( rs.getInt( "PurchaseOrder_Id" ) );
				purchaseOrder.setPurchaseOrderNumber( rs.getString( "PONumber" ) );
				purchaseOrder.setVendorName( rs.getString( "Vendor_Name" ) );
                purchaseOrder.setVendor_Id( rs.getInt("vendor_id"));
				purchaseOrder.setPurchaseOrderDate( rs.getString( "PODate" ) );
				purchaseOrder.setValidFromDate( rs.getString( "Valid_From" ) );
				purchaseOrder.setValidToDate( rs.getString( "Valid_To" ) );
				purchaseOrder.setPoMaxAmt( rs.getString( "MAX_Value" ) );
				purchaseOrder.setPoRemainingAmt( rs.getString( "Remaining_Value" ) );

				purchaseOrderList.add( purchaseOrder );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Vendors ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Vendors" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Vendors" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Vendors" );
				}
			}
		}
		return purchaseOrderList;
	}

	/*
	 * Returns the Purchase Order Number
	 */
	public String loadAutomatedPurchaseOrderNo() throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		String purchaseOrderNo 	= null;
		String number 			= null;
		String finalPurchaseOrderNo 	= null;
		String purchaseOrderPrefix 		= null;
		int purchaseOrderLength = 0;
		int incrementBy 		= 1;

		try
		{
			conn  	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
			rs 		= pstmt.executeQuery();

			if( rs.next() )
			{
				purchaseOrderPrefix = rs.getString( "Purchase_Order_Prefix" );
				purchaseOrderLength = rs.getInt( "Purchase_Order_Length" );
			}
			rs.close();
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement( "select PurchaseOrder_Id, PurchaseOrder_No from PurchaseOrder_Table where PurchaseOrder_Id = ( select max( PurchaseOrder_Id ) from PurchaseOrder_Table where ( ( OpenPO_Id = -1 ) or ( OpenPO_Id IS null ) ) )" );
			rs    = pstmt.executeQuery();

			if( rs.next() )
			{
				purchaseOrderNo = rs.getString ( "PurchaseOrder_No" );
			}
			else
			{
				purchaseOrderNo = "0";
			}

			try
			{
				if( purchaseOrderNo != null )
				{
					int indexValue = purchaseOrderNo.indexOf( "-" );
					number = purchaseOrderNo.substring( indexValue+1 );
					String no = (Long.parseLong (number) + incrementBy )+"";
					int noLength = no.length();
					int totalLength = purchaseOrderLength - noLength ;
					if( totalLength > 0 )
					{
						for(int  i = 0; i < totalLength ; i++  )
						{
							no = "0"+no;
						}
					}

					if( purchaseOrderPrefix != null && purchaseOrderPrefix.trim().length() != 0 )
					{
						finalPurchaseOrderNo = purchaseOrderPrefix+"-"+no ;
					}
					else
					{
						finalPurchaseOrderNo = no;
					}
				}
			}
			catch (NumberFormatException nfe)
			{
				finalPurchaseOrderNo = ( rs.getInt ( "PurchaseOrder_Id" ) + incrementBy )+"" ;
			}
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not get Automated Purchase Order No from database", sqlex );
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException excp) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException excp) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException excp) {}
			}
		}
		return finalPurchaseOrderNo;
    }

	/*
	 * returns the Client address
	 */
	public Address getClientAddress() throws ScalarException
	{
		Address clientAddress 	= null;
		ClientInfo clientInfo 	= UserDAO.getClientAddressInfo();
		clientAddress 			= clientInfo.getAddress();
		clientAddress.setContactPerson( clientInfo.getClientName() );
		return clientAddress;
	}

	/*
	 * Checks wether the Purchase Order Number Exists
	 */
	public boolean isPurchaseOrderNumberExists(String poNumber) throws ScalarException
	{
		boolean poExists 		= false;
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareStatement( "select PurchaseOrder_Id from PurchaseOrder_Table where PurchaseOrder_No = ?" );
			pstmt.setString( 1, poNumber );
			rs 		= pstmt.executeQuery();

			if( rs.next() )
			{
				poExists = true;
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Check Purchase Order Number Exists ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Check Purchase Order Number Exists" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Check Purchase Order Number Exists" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Check Purchase Order Number Exists" );
				}
			}
		}
		return poExists;
	}

	/*
	 * Saves the Open Purchase Order
	 */
	public void saveOpenPurchaseOrder(OpenPurchaseOrderForm purchaseOrderForm, String userName) throws ScalarException
	{
		DBTransaction dbTransaction = null;
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs			= null;
		int revesionNumber 		= 1;
		boolean success	= false;

		try
		{
			dbTransaction = DBTransaction.getInstance();

			if( dbTransaction != null )
			{
				dbTransaction.begin();
			}
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            else
            {
                conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            if ( purchaseOrderForm.getShippingMethodId() == -2 )
            {
                pstmt = conn.prepareStatement ( "select Shipping_Method_ID FROM Shipping_Methods_Table WHERE SM_CUSTOMER_ID = -1 AND SHIPPING_METHOD = ? AND SHIPPING_ACCOUNT = ? " );
                pstmt.setString ( 1, purchaseOrderForm.getNewShippingMethod() != null ?  purchaseOrderForm.getNewShippingMethod().trim() : "" );
                pstmt.setString ( 2, purchaseOrderForm.getNewShippingAccount() != null ? purchaseOrderForm.getNewShippingAccount().trim() : "" );
                rs = pstmt.executeQuery();
                if( rs.next() )
                {
                    purchaseOrderForm.setShippingMethodId( rs.getInt ( "Shipping_Method_ID" ) );
                }
                else //shipping method not present..need to insert
                {
                    pstmt.close();
                    rs.close();
                    pstmt = conn.prepareStatement ( "INSERT INTO SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES ( -1, ?, ? )" );
                    pstmt.setString ( 1, purchaseOrderForm.getNewShippingMethod() != null ?  purchaseOrderForm.getNewShippingMethod().trim() : "" );
                    pstmt.setString ( 2, purchaseOrderForm.getNewShippingAccount() != null ? purchaseOrderForm.getNewShippingAccount().trim() : "" );
                    pstmt.executeUpdate();

                    pstmt.close();
                    rs.close();

                    pstmt = conn.prepareStatement ( "SELECT @@IDENTITY AS SHIPPING_METHOD_ID" );
                    rs = pstmt.executeQuery();
                    if ( rs.next() )
                    {
                        purchaseOrderForm.setShippingMethodId( rs.getInt ( "SHIPPING_METHOD_ID" ) );
                    }
                }
                pstmt.close();
                rs.close();
            }
            if ( "-2".equals( purchaseOrderForm.getPayTerms() ) )
            {
                pstmt = conn.prepareStatement ( "SELECT COUNT(*) CNT FROM SHIPPINGTERMS WHERE SHIPPINGTERMS = ?" );
                pstmt.setString ( 1, purchaseOrderForm.getNewPayTerms() != null ? purchaseOrderForm.getNewPayTerms().trim() : "" );
                rs = pstmt.executeQuery();
                int cnt = 0;
                if( rs.next() )
                {
                    cnt = rs.getInt ( "CNT" );
                }
                if ( cnt == 0 ) //pay terms not present..need to insert
                {
                    pstmt.close();
                    rs.close();
                    pstmt = conn.prepareStatement ( "INSERT INTO SHIPPINGTERMS( SHIPPINGTERMS ) VALUES ( ? )" );
                    pstmt.setString ( 1, purchaseOrderForm.getNewPayTerms() != null ? purchaseOrderForm.getNewPayTerms().trim() : "" );

                    pstmt.executeUpdate();
                }
                purchaseOrderForm.setPayTerms( purchaseOrderForm.getNewPayTerms() != null ? purchaseOrderForm.getNewPayTerms().trim() : "" );

                pstmt.close();
                rs.close();
            }
            if( purchaseOrderForm.getPurchaseOrder_id() == -1 )
			{
				setPurchaseOrderShippingAddress( dbTransaction, purchaseOrderForm );

                pstmt 	= conn.prepareStatement( "INSERT INTO PurchaseOrder_Table( PurchaseOrder_No, PurchaseOrder_Type, Vendor_Id, PO_Date, VendorQuote, PO_Max_value, PO_Valid_From, PO_Valid_To, PO_revision_No, PO_Shipping_Address_Id, PO_Billing_Address_ID, PO_Finance_Hold, PO_Operation_Hold, PO_Comments, PO_Status, PO_Created_By, PO_Last_Modified_On, PO_Last_Modifed_By, PO_Is_Cancel, SHIPPING_METHOD_ID, PAY_TERMS ) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?,?, ?, ? ) " );

				pstmt.setString	( 1, purchaseOrderForm.getPoNumber() );
				pstmt.setInt	( 2, purchaseOrderForm.getPoType() );
				pstmt.setInt	( 3, purchaseOrderForm.getVendor_id() );
				pstmt.setString ( 4, purchaseOrderForm.getPoDate() );
				pstmt.setString ( 5, purchaseOrderForm.getVendorQuoteNumber() );
				pstmt.setString ( 6, purchaseOrderForm.getPoMaxAmt() );
				pstmt.setString ( 7, purchaseOrderForm.getValidFromDate() );
				pstmt.setString	( 8, purchaseOrderForm.getValidToDate() );
				pstmt.setInt 	( 9, 1 );
				pstmt.setInt	( 10, purchaseOrderForm.getShippingAddress_id() );
				pstmt.setInt	( 11, purchaseOrderForm.getBillingAddress_id() );
				pstmt.setInt	( 12, purchaseOrderForm.getFinanceHold() );
				pstmt.setInt	( 13, purchaseOrderForm.getOperationsHold() );
				pstmt.setString	( 14, purchaseOrderForm.getComments() );

                if( purchaseOrderForm.getFinanceHold() == 0 && purchaseOrderForm.getOperationsHold() == 0 )
                {
                    pstmt.setString	( 15, PURCHASE_ORDER_STATUS_RELEASED );
                }
                else
                {
                    pstmt.setString	( 15, PURCHASE_ORDER_STATUS_NEW );
                }
				pstmt.setString	( 16, userName );
				pstmt.setString	( 17, userName );
                pstmt.setInt    ( 18, 1 );
                pstmt.setInt    ( 19, purchaseOrderForm.getShippingMethodId() );
                pstmt.setString ( 20, purchaseOrderForm.getPayTerms() );

                pstmt.executeUpdate();

				pstmt.close();
				pstmt = null;

				pstmt = conn.prepareStatement( "Select @@IDENTITY as purchaseOrder_Id" );
				rs = pstmt.executeQuery();
				if( rs.next() )
				purchaseOrderForm.setPurchaseOrder_id( rs.getInt( "purchaseOrder_Id" ) );

				pstmt.close();

				pstmt 	= conn.prepareStatement( "INSERT INTO PurchaseOrder_Audit_Trail( PurchaseOrder_ID, [User_Id], Actions, TimeStamp )VALUES( ?, ?, ?, CURRENT_TIMESTAMP )" );
				pstmt.setInt( 1, purchaseOrderForm.getPurchaseOrder_id() );
				pstmt.setString( 2, userName );
				pstmt.setString( 3, "<BR>Created new open purchase order" );

				pstmt.executeUpdate();
			}
			else
			{
				revesionNumber = fetchRevisionNumber( dbTransaction, purchaseOrderForm, userName );
                double poTotal = 0.0;
                //pstmt = conn.prepareStatement("select ISNULL( SUM( po_total), 0) as poTotal from purchaseOrder_table where openPO_id = ? and po_status not like 'Cancelled'");
                pstmt = conn.prepareStatement("SELECT ISNULL(SUM(PR_Total),0) as poTotal FROM PurchaseOrder_Receipt_Table PR WHERE PR.PURCHASEORDER_ID = ? AND PR.PR_STATUS != 'Cancelled'");
                pstmt.setInt(1, purchaseOrderForm.getPurchaseOrder_id());
                rs = pstmt.executeQuery();
                if( rs.next() )
                {
                    poTotal = rs.getDouble("poTotal");
                }

                pstmt.close();
        		pstmt 	= conn.prepareStatement( "UPDATE PurchaseOrder_Table SET VendorQuote = ?, PO_Max_value = ?, PO_Valid_From = ?, PO_Valid_To = ?, PO_revision_No = ?, PO_Finance_Hold = ?, PO_Operation_Hold = ?, PO_Comments = ?,PO_Is_Cancel = ? , PO_Status = ?, PO_Last_Modified_On = CURRENT_TIMESTAMP, PO_Last_Modifed_By =?, SHIPPING_METHOD_ID = ?, PAY_TERMS = ? WHERE PurchaseOrder_ID = ?" );

                boolean prevCancel  = purchaseOrderForm.isPrevCancel();
                boolean cancel      = purchaseOrderForm.isCancel();
                String status       = PURCHASE_ORDER_STATUS_NEW;

                if( "searchChangePurchaseOrder".equalsIgnoreCase(purchaseOrderForm.getPath()) )
                {
                    if( prevCancel == cancel )
                    {
                        status = purchaseOrderForm.getPoStatus();
                    }                    
                    if( poTotal >= Double.parseDouble(purchaseOrderForm.getPoMaxAmt()))
                    {
                        status = PURCHASE_ORDER_STATUS_CLOSED ;
                    }
                    if( prevCancel == false && cancel == true )
                    {
                        purchaseOrderForm.setFinanceHold( -1);
                        purchaseOrderForm.setOperationsHold( -1);
                        status = PURCHASE_ORDER_STATUS_NEW;
                    }
                }
                else
                {
                    if( cancel == true  )
                    {
                        if( purchaseOrderForm.getFinanceHold() == 0  && purchaseOrderForm.getOperationsHold() == 0 )
                        {
                            status = PURCHASE_ORDER_STATUS_CANCELLED;
                        }
                    }
                    else
                    {
                        if( purchaseOrderForm.getFinanceHold() == 0  && purchaseOrderForm.getOperationsHold() == 0 )
                        {
                            status = PURCHASE_ORDER_STATUS_RELEASED;
                        }
                    }
                }

                pstmt.setString ( 1, purchaseOrderForm.getVendorQuoteNumber() );
                pstmt.setString ( 2, purchaseOrderForm.getPoMaxAmt() );
                pstmt.setString ( 3, purchaseOrderForm.getValidFromDate() );
                pstmt.setString	( 4, purchaseOrderForm.getValidToDate() );
                pstmt.setInt 	( 5, revesionNumber );
                pstmt.setInt	( 6, purchaseOrderForm.getFinanceHold() );
                pstmt.setInt	( 7, purchaseOrderForm.getOperationsHold() );
                pstmt.setString	( 8, purchaseOrderForm.getComments() );
                pstmt.setInt    ( 9, cancel == true ? 0:1);
                pstmt.setString ( 10, status );
				pstmt.setString	( 11, userName );
                pstmt.setInt    ( 12, purchaseOrderForm.getShippingMethodId() );
                pstmt.setString ( 13, purchaseOrderForm.getPayTerms() );
                pstmt.setInt	( 14, purchaseOrderForm.getPurchaseOrder_id() );

				pstmt.executeUpdate();
			}
			success = true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Save Purchase Order ", e );
		}
		finally
		{
			try
			{
				if( dbTransaction == null )
				{
					if( conn != null )
					{
						if( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				else
				{
					try
					{
						if( success )
						{
							dbTransaction.commit();
						}
						else
						{
							dbTransaction.rollback();
						}
					}
					catch( DBXException e )
					{
						e.printStackTrace();
					}
				}
			}
			catch (SQLException ignore) {}



			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the record set in Save Purchase Order" );
				}
			}

			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Save Purchase Order" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Save Purchase Order" );
				}
			}
		}
	}

	/**
	 * @param dbTransaction
	 * @param purchaseOrderForm

	private void setPurchaseOrderBillingAddress(DBTransaction dbTransaction, OpenPurchaseOrderForm purchaseOrderForm) throws ScalarException
	{
		Connection conn			= null;
		PreparedStatement pstmt	= null;
		ResultSet rs			= null;
		Address address 		= null;
		int newBillingAddress_Id = -1;
		boolean success			= false;

		try
		{
			address = PurchaseOrderDAO.getClientAddress();

			if( dbTransaction == null )
			{
				conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}
			else
			{
				conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}

			if( address != null )
			{
				newBillingAddress_Id = VendorDAO.saveAddress( dbTransaction, address, true );
				purchaseOrderForm.setBillingAddress_id( newBillingAddress_Id );
			}

			success	= true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not save Billing Address", e );
		}
		finally
		{
			if( dbTransaction == null )
			{
				try
				{
					if( conn != null )
					{
						if( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch( Exception e ){}
			}
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in saving Billing Address" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in saving Billing Address" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in saving Billing Address" );
				}
			}
		}
	}*/

	/**
	 * @param purchaseOrderForm
	 */
	private void setPurchaseOrderShippingAddress( DBTransaction dbTransaction, OpenPurchaseOrderForm purchaseOrderForm) throws ScalarException
	{
		Connection conn			= null;
		PreparedStatement pstmt	= null;
		ResultSet rs			= null;
		Address address 		= null;
		String contactPersonAddress_Id 	= purchaseOrderForm.getContactPerson();
		int newShippingAddress_Id = -1;
		boolean success			= false;

        String vendorSalesAddress_Id 	= purchaseOrderForm.getVendorAddressId();

        if( vendorSalesAddress_Id != null   && !vendorSalesAddress_Id.equalsIgnoreCase("-1") )
        {
            ArrayList salesAddressList 	= PurchaseOrderDAO.loadVendorSalesAddresses( purchaseOrderForm.getVendor_id() );
            for( int i = 0; i < salesAddressList.size(); i++ )
			{
				address = (Address)salesAddressList.get( i );
				if( vendorSalesAddress_Id.equalsIgnoreCase( address.getContactPersonAddressId()+"" ) )
				{
					break;
				}
			}
        }
        else if( contactPersonAddress_Id != null && ! contactPersonAddress_Id.equalsIgnoreCase("-1"))
		{
            ArrayList addressList 	= PurchaseOrderDAO.loadVendorShippingAddresses( purchaseOrderForm.getVendor_id() );
            for( int i = 0; i < addressList.size(); i++ )
			{
				address = (Address)addressList.get( i );
				if( contactPersonAddress_Id.equalsIgnoreCase( address.getContactPersonAddressId()+"" ) )
				{
					break;
				}
			}
		}

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}
			else
			{
				conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}

			if( address != null )
			{
				newShippingAddress_Id = VendorDAO.saveAddress( dbTransaction, address, true );
				purchaseOrderForm.setShippingAddress_id( newShippingAddress_Id );
			}

			success	= true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not save Shipping Address", e );
		}
		finally
		{
			if( dbTransaction == null )
			{
				try
				{
					if( conn != null )
					{
						if( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch( Exception e ){}
			}
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in saving Shipping Address" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in saving Shipping Address" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in saving Shipping Address" );
				}
			}
		}
	}

	/**
	 * @param purchaseOrderForm
	 */
	private void setPurchaseOrderShippingAddress( DBTransaction dbTransaction, PurchaseOrderForm purchaseOrderForm) throws ScalarException
	{
		Connection conn			= null;
		PreparedStatement pstmt	= null;
		ResultSet rs			= null;
		ArrayList addressList 	= PurchaseOrderDAO.loadVendorShippingAddresses( purchaseOrderForm.getVendor_id() );
		Address address 		= null;
		String contactPersonAddress_Id 	= purchaseOrderForm.getContactPerson();
		int newShippingAddress_Id = -1;
		boolean success			= false;

		if( contactPersonAddress_Id != null )
		{
			for( int i = 0; i < addressList.size(); i++ )
			{
				address = (Address)addressList.get( i );
				if( contactPersonAddress_Id.equalsIgnoreCase( address.getContactPersonAddressId()+"" ) )
				{
					break;
				}
			}
		}

		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}
			else
			{
				conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}

			if( address != null )
			{
				newShippingAddress_Id = VendorDAO.saveAddress( dbTransaction, address, true );
				purchaseOrderForm.setShippingAddress_id( newShippingAddress_Id );
			}

			success	= true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not save Shipping Address", e );
		}
		finally
		{
			if( dbTransaction == null )
			{
				try
				{
					if( conn != null )
					{
						if( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch( Exception e ){}
			}
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in saving Shipping Address" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in saving Shipping Address" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in saving Shipping Address" );
				}
			}
		}
	}


	/**
	 * @param purchaseOrderForm
	 * @return
	 */
	public int fetchRevisionNumber( DBTransaction dbTransaction, OpenPurchaseOrderForm purchaseOrderForm, String userName ) throws ScalarException
	{
		int revesionNumber = 1;
		Connection conn         = null;
		CallableStatement cstmt = null;
		ResultSet rs            = null;


		boolean success			= false;


		try
		{
			if( dbTransaction == null )
			{
				conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}
			else
			{
				conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			}

			cstmt 	= conn.prepareCall( "{ call sp_open_purchase_order_audit_trail( ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
			cstmt.setInt   ( 1, purchaseOrderForm.getPurchaseOrder_id() );
			cstmt.setString( 2, purchaseOrderForm.getPoMaxAmt() );
			cstmt.setString( 3, purchaseOrderForm.getValidFromDate() );
			cstmt.setString( 4, purchaseOrderForm.getValidToDate() );
			cstmt.setString( 5, purchaseOrderForm.getVendorQuoteNumber() );
			cstmt.setInt   ( 6, purchaseOrderForm.getFinanceHold() );
			cstmt.setInt   ( 7, purchaseOrderForm.getOperationsHold() );
			cstmt.setString( 8, userName );
			cstmt.registerOutParameter( 9, Types.INTEGER );
			cstmt.execute();

			revesionNumber = cstmt.getInt( 9 );
			success			= true;
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not update Audit Trail", e );
		}
		finally
		{
			if( dbTransaction == null )
			{
				try
				{
					if( conn != null )
					{
						if( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch( Exception e ){}
			}
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in update Audit Trail" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in update Audit Trail" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in update Audit Trail" );
				}
			}
		}
		return revesionNumber;
	}


    public int fetchRevisionNumber( DBTransaction dbTransaction, PurchaseOrderForm purchaseOrderForm, String userName ) throws ScalarException
    {
        int dbRevesionNumber        = 1;
        int revesionNumber          = 1;
        int lineItemRevesionNumber  = 1;
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rs            = null;
        Iterator lineItemIterator   = null;
        PurchaseOrderLineItem purchaseOrderLineItem = null;

        boolean success			= false;

        try
        {
            dbRevesionNumber = purchaseOrderForm.getRevisionNo();
            revesionNumber   = purchaseOrderForm.getRevisionNo();
            lineItemRevesionNumber = purchaseOrderForm.getRevisionNo();

            if( dbTransaction == null )
            {
                conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            else
            {
                conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }

            cstmt 	= conn.prepareCall( "{ call sp_purchase_order_audit_trail( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            cstmt.setInt    (  1, purchaseOrderForm.getPurchaseOrder_Id() );
            cstmt.setString (  2, userName );
            cstmt.setString (  3, purchaseOrderForm.getVendorQuoteNumber() );
            cstmt.setInt    (  4, 1  );                // Here "1" represents that Procedure is calling for Purchase Order
            cstmt.setInt    (  5, -1 );
            cstmt.setString (  6, "" );
            cstmt.setString (  7, "" );
            cstmt.setString (  8, "" );
            cstmt.setLong   (  9, -1 );
            cstmt.setLong   ( 10, -1 );
            cstmt.setLong   ( 11, -1 );
            cstmt.setLong   ( 12, -1 );
            cstmt.setString ( 13, "" );
            cstmt.setLong   ( 14, -1 );
            cstmt.setLong   ( 15, -1 );
            cstmt.setLong   ( 16, -1 );
            cstmt.setDouble ( 17, -1 );
            cstmt.setDouble ( 18, -1 );
            cstmt.setDouble ( 19, -1 );
            cstmt.setDouble ( 20, -1 );
            cstmt.setDouble ( 21, -1 );
            cstmt.setDouble ( 22, -1 );
            cstmt.setString ( 23, "" );

            cstmt.registerOutParameter( 24, Types.INTEGER );
            cstmt.execute();
            revesionNumber = cstmt.getInt( 24 );

            lineItemIterator    = purchaseOrderForm.getPurchaseOrderLineItemList().iterator();
            while ( lineItemIterator.hasNext() )
            {
                purchaseOrderLineItem = ( PurchaseOrderLineItem ) lineItemIterator.next();

                cstmt.setInt    (  1, purchaseOrderForm.getPurchaseOrder_Id() );
                cstmt.setString (  2, userName );
                cstmt.setString (  3, purchaseOrderForm.getVendorQuoteNumber() );
                cstmt.setInt    (  4, 0 );            // Here "0" represents that Procedure is calling for Purchase Order line item.
                cstmt.setInt    (  5, purchaseOrderLineItem.getPurchaseOrderLineItem_Id() );
                cstmt.setString (  6, purchaseOrderLineItem.getPackageType() );
                cstmt.setString (  7, purchaseOrderLineItem.getRequestedDate() );
                cstmt.setString (  8, purchaseOrderLineItem.getPromisedDate() );
                cstmt.setLong   (  9, purchaseOrderLineItem.getRequestedQuantity() );
                cstmt.setLong   ( 10, purchaseOrderLineItem.getShippedQty() );
                cstmt.setDouble ( 11, purchaseOrderLineItem.getUnitCost() );
                cstmt.setDouble ( 12, purchaseOrderLineItem.getExtendedCost() );
                cstmt.setString ( 13, purchaseOrderLineItem.getStatus() );
                cstmt.setInt    ( 14, purchaseOrderLineItem.getCancel() ? 0 : 1 );
                cstmt.setInt    ( 15, purchaseOrderLineItem.getFinanceHold() );
                cstmt.setInt    ( 16, purchaseOrderLineItem.getOperationsHold() );
                cstmt.setDouble ( 17, -1 );
                cstmt.setDouble ( 18, -1 );
                cstmt.setDouble ( 19, -1 );
                cstmt.setDouble ( 20, -1 );
                cstmt.setDouble ( 21, -1 );
                cstmt.setDouble ( 22, -1 );
                cstmt.setString ( 23, "" );

                cstmt.registerOutParameter( 24, Types.INTEGER );
                cstmt.execute();
                lineItemRevesionNumber = cstmt.getInt( 24 );
            }

            cstmt.setInt    (  1, purchaseOrderForm.getPurchaseOrder_Id() );
            cstmt.setString (  2, userName );
            cstmt.setString (  3, "" );
            cstmt.setInt    (  4, 2  );                // Here "2" represents that Procedure is calling for Purchase Order, and for values that lie below the line items, like subtotal, freight etc..
            cstmt.setInt    (  5, -1 );
            cstmt.setString (  6, "" );
            cstmt.setString (  7, "" );
            cstmt.setString (  8, "" );
            cstmt.setLong   (  9, -1 );
            cstmt.setLong   ( 10, -1 );
            cstmt.setLong   ( 11, -1 );
            cstmt.setLong   ( 12, -1 );
            cstmt.setString ( 13, "" );
            cstmt.setLong   ( 14, -1 );
            cstmt.setLong   ( 15, -1 );
            cstmt.setLong   ( 16, -1 );
            cstmt.setDouble ( 17, purchaseOrderForm.getSubTotal() );
            cstmt.setDouble ( 18, purchaseOrderForm.getFreight());
            cstmt.setDouble ( 19, purchaseOrderForm.getTradeDiscount());
            cstmt.setDouble ( 20, purchaseOrderForm.getMiscellaneous());
            cstmt.setDouble ( 21, purchaseOrderForm.getTax());
            cstmt.setDouble ( 22, purchaseOrderForm.getTotal());
            cstmt.setString ( 23, purchaseOrderForm.getPoTaxType());


            cstmt.registerOutParameter( 24, Types.INTEGER );
            cstmt.execute();
            revesionNumber = cstmt.getInt( 24 );


            if( ( dbRevesionNumber != revesionNumber ) || ( dbRevesionNumber != lineItemRevesionNumber ) )
            {
                revesionNumber =  dbRevesionNumber + 1;
            }

            success			= true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not update Audit Trail", e );
        }
        finally
        {
            if( dbTransaction == null )
            {
                try
                {
                    if( conn != null )
                    {
                        if( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch( Exception e ){}
            }
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in update Audit Trail" );
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in update Audit Trail" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in update Audit Trail" );
                }
            }
        }
        return revesionNumber;
    }


	/*
	 *
	 */
	public void loadOpenPurchaseOrder(OpenPurchaseOrderForm purchaseOrderForm) throws ScalarException
	{
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareStatement( "SELECT PurchaseOrder_Id, PurchaseOrder_No, PurchaseOrder_Type, PO.Vendor_Id, CONVERT( VARCHAR, PO_DATE, 101 )AS PO_Date, VendorQuote, PO_Max_Value, CONVERT( VARCHAR, PO_Valid_From, 101) as PO_Valid_From, CONVERT( VARCHAR, PO_Valid_To, 101) as PO_Valid_To, PO_revision_No, PO_Shipping_Address_Id, PO_Billing_Address_Id, PO_Finance_Hold, PO_Operation_Hold, PO_Comments, PO_Status, CONVERT( CHAR, PO_Last_Modified_On, 101 ) PO_Last_Modified_On, V_Name, PO_Is_Cancel, ISNULL((SELECT SUM(PR_Total) FROM PurchaseOrder_Receipt_Table PR WHERE PR.PURCHASEORDER_ID = PO.PurchaseOrder_Id AND PR.PR_STATUS != 'Cancelled'), 0) AS IssuedAmt, SHIPPING_METHOD_ID, PAY_TERMS, (SELECT SHIPPING_METHOD + '   #   ' + SHIPPING_ACCOUNT FROM SHIPPING_METHODS_TABLE WHERE SHIPPING_METHOD_ID = PO.SHIPPING_METHOD_ID) AS SHIP_METHOD FROM PurchaseOrder_Table PO, Vendor WHERE PO.Vendor_Id = Vendor.Vendor_ID And PO.PurchaseOrder_Id = ?" );
			pstmt.setInt	( 1, purchaseOrderForm.getPurchaseOrder_id() );
			rs 		= pstmt.executeQuery();

			if( rs.next() )
			{
				purchaseOrderForm.setPoNumber( rs.getString( "PurchaseOrder_No" ) );
				purchaseOrderForm.setPoType  ( rs.getInt( "PurchaseOrder_Type" ) );
				purchaseOrderForm.setVendor_id( rs.getInt( "Vendor_Id" ) );
				purchaseOrderForm.setPoDate( rs.getString( "PO_Date" ) );
				purchaseOrderForm.setVendorQuoteNumber( rs.getString( "VendorQuote" ) );
				purchaseOrderForm.setPoMaxAmt( rs.getString( "PO_Max_Value" ) );
				purchaseOrderForm.setValidFromDate( rs.getString( "PO_Valid_From" ) );
				purchaseOrderForm.setValidToDate( rs.getString( "PO_Valid_To" ) );
				purchaseOrderForm.setPoRev( rs.getInt( "PO_revision_No" ) );
				purchaseOrderForm.setShippingAddress_id( rs.getInt( "PO_Shipping_Address_Id" ) );
				purchaseOrderForm.setBillingAddress_id( rs.getInt( "PO_Billing_Address_Id" ) );
				purchaseOrderForm.setFinanceHold( rs.getInt( "PO_Finance_Hold" ) );
				purchaseOrderForm.setOperationsHold( rs.getInt( "PO_Operation_Hold" ) );
				purchaseOrderForm.setComments( rs.getString( "PO_Comments" ) );
				purchaseOrderForm.setPoStatus( rs.getString( "PO_Status" ) );
				purchaseOrderForm.setPoLastModifiedDate( rs.getString( "PO_Last_Modified_On" ) );
				purchaseOrderForm.setVendorName( rs.getString( "V_Name" ) );

                //added by Murali Krishna on 24/3/05
                purchaseOrderForm.setCancel( rs.getInt("PO_Is_Cancel") == 0 ? true:false );
                purchaseOrderForm.setPrevCancel( purchaseOrderForm.isCancel());
                //end

				purchaseOrderForm.setPoIssuedAmt( rs.getString( "IssuedAmt" ) );
                purchaseOrderForm.setShippingMethodId( rs.getInt ( "SHIPPING_METHOD_ID" ) );
                purchaseOrderForm.setPayTerms( rs.getString ( "PAY_TERMS" ) );
                purchaseOrderForm.setShipMethod( rs.getString ( "SHIP_METHOD" ) );
            }
			rs.close();
			pstmt.close();

            purchaseOrderForm.setBillingAddress( PurchaseOrderDAO.getAddress( purchaseOrderForm.getBillingAddress_id() ) );
		    purchaseOrderForm.setShippingAddress( PurchaseOrderDAO.getAddress( purchaseOrderForm.getShippingAddress_id() ) );
            purchaseOrderForm.setNewShippingMethod( null );
            purchaseOrderForm.setNewShippingAccount( null );
            purchaseOrderForm.setNewPayTerms( null );

        }
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Check Purchase Order Number Exists ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Check Purchase Order Number Exists" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Check Purchase Order Number Exists" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Check Purchase Order Number Exists" );
				}
			}
		}
	}

    /*
     * Search All Purchase Orders
     */
    public ArrayList searchPurchaseOrder(PurchaseOrderSearchForm purchaseOrderSearchForm) throws ScalarException
    {
        ArrayList purchaseOrderList = new ArrayList();
        Connection conn         	= null;
        CallableStatement cstmt 	= null;
        ResultSet rs            	= null;
        PurchaseOrder purchaseOrder = null;
        String purchaseOrderNo		= "%";
        try
        {
            if ( purchaseOrderSearchForm == null )
            {
                purchaseOrderSearchForm = new PurchaseOrderSearchForm();
            }

            conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            cstmt 	= conn.prepareCall( "{ call sp_search_pos( ?, ?, ?, ?, ?, ?, ? ) }" );

            if( purchaseOrderSearchForm.getPoNumber() != null )
            {
				purchaseOrderNo	= purchaseOrderSearchForm.getPoNumber().trim() + "%";
            }

            cstmt.setString	( 1, purchaseOrderNo );
            cstmt.setInt   	( 2, purchaseOrderSearchForm.getVendor_id() );
            cstmt.setInt	( 3, purchaseOrderSearchForm.getPoType() );
            cstmt.setString	( 4, purchaseOrderSearchForm.getStatus() );
            cstmt.setInt	( 5, purchaseOrderSearchForm.getProcessStep_Id() );
            cstmt.setString	( 6, purchaseOrderSearchForm.getYear() );

            if( ACTION_CHANGE_PURCHASE_ORDER_SEARCH.equalsIgnoreCase( purchaseOrderSearchForm.getAction() ) )
            {
                cstmt.setInt( 7, 2 );
            }
            else if ( ACTION_PENDING_PURCHASE_ORDER_SEARCH.equalsIgnoreCase( purchaseOrderSearchForm.getAction() ) )
            {
                cstmt.setInt( 7, 3 );
            }
            else if( ACTION_CANCELLED_PURCHASE_ORDER_SEARCH.equalsIgnoreCase( purchaseOrderSearchForm.getAction() ) )
            {
                cstmt.setInt( 7, 4 );
            }
            else
            {
                cstmt.setInt( 7, 1 );
            }

            rs 		= cstmt.executeQuery();

            while( rs.next() )
            {
                purchaseOrder = new PurchaseOrder();
                purchaseOrder.setPurchaseOrder_Id( rs.getInt( "PurchaseOrder_Id" ) );
                purchaseOrder.setPurchaseOrderNumber( rs.getString( "PONumber" ) );
                purchaseOrder.setVendorName( rs.getString( "Vendor_Name" ) );
                purchaseOrder.setPurchaseOrderDate( rs.getString( "PODate" ) );
                purchaseOrder.setStatus( rs.getString( "POStatus" ) );
                purchaseOrder.setPurchaseOrderType( rs.getInt( "POType" ) );
                purchaseOrder.setValidFromDate( rs.getString( "Valid_From" ) );
                purchaseOrder.setValidToDate( rs.getString( "Valid_To" ) );
                purchaseOrder.setPoMaxAmt( rs.getString( "MAX_Value" ) );
                //purchaseOrder.setPoRemainingAmt( rs.getString( "Remaining_Value" ) );

                purchaseOrderList.add( purchaseOrder );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException( "Could not Search Purchase Orders", e );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Search Purchase Orders" );
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Search Purchase Orders" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Search Purchase Orders" );
                }
            }
        }
        return purchaseOrderList;
    }

	/*
	 * Returns All The PO Products
	 */
	public ArrayList loadPOProductsList() throws ScalarException
	{
		ArrayList productList = new ArrayList();
		Connection conn         = null;
		CallableStatement cstmt = null;
		ResultSet rs            = null;
		LabelValueBean bean 	= null;

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			cstmt 	= conn.prepareCall( "{ call sp_load_all_po_products() }" );
			rs 		= cstmt.executeQuery();

			while( rs.next() )
			{
				bean = new LabelValueBean( rs.getString( "Part_Number" ), rs.getString( "ProcessStep_Id" ) );
				productList.add( bean );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Products ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Products" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Products" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Products" );
				}
			}
		}

		return productList;
	}


    public void savePurchaseOrder( PurchaseOrderForm purchaseOrderForm, String userName ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rs                = null;
        DBTransaction dbTransaction = null;

        Iterator lineItemIterator   = null;
		Date date                   = new Date( System.currentTimeMillis() );
        boolean success             = false;
        int purchaseOrderId         = -1;
		int purchaseOrderLineItemId = -1;
        int revesionNumber          = 1;

        try
        {

            dbTransaction = DBTransaction.getInstance();

            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            else
            {
                conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            if ( purchaseOrderForm.getShippingMethodId() == -2 )
            {
                pstmt = conn.prepareStatement ( "select Shipping_Method_ID FROM Shipping_Methods_Table WHERE SM_CUSTOMER_ID = -1 AND SHIPPING_METHOD = ? AND SHIPPING_ACCOUNT = ? " );
                pstmt.setString ( 1, purchaseOrderForm.getNewShippingMethod() != null ?  purchaseOrderForm.getNewShippingMethod().trim() : "" );
                pstmt.setString ( 2, purchaseOrderForm.getNewShippingAccount() != null ? purchaseOrderForm.getNewShippingAccount().trim() : "" );
                rs = pstmt.executeQuery();
                if( rs.next() )
                {
                    purchaseOrderForm.setShippingMethodId( rs.getInt ( "Shipping_Method_ID" ) );
                }
                else //shipping method not present..need to insert
                {
                    pstmt.close();
                    rs.close();
                    pstmt = conn.prepareStatement ( "INSERT INTO SHIPPING_METHODS_TABLE( SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT ) VALUES ( -1, ?, ? )" );
                    pstmt.setString ( 1, purchaseOrderForm.getNewShippingMethod() != null ?  purchaseOrderForm.getNewShippingMethod().trim() : "" );
                    pstmt.setString ( 2, purchaseOrderForm.getNewShippingAccount() != null ? purchaseOrderForm.getNewShippingAccount().trim() : "" );
                    pstmt.executeUpdate();

                    pstmt.close();
                    rs.close();

                    pstmt = conn.prepareStatement ( "SELECT @@IDENTITY AS SHIPPING_METHOD_ID" );
                    rs = pstmt.executeQuery();
                    if ( rs.next() )
                    {
                        purchaseOrderForm.setShippingMethodId( rs.getInt ( "SHIPPING_METHOD_ID" ) );
                    }
                }
                pstmt.close();
                rs.close();
            }
            if ( "-2".equals( purchaseOrderForm.getPayTerms() ) )
            {
                pstmt = conn.prepareStatement ( "SELECT COUNT(*) CNT FROM SHIPPINGTERMS WHERE SHIPPINGTERMS = ?" );
                pstmt.setString ( 1, purchaseOrderForm.getNewPayTerms() != null ? purchaseOrderForm.getNewPayTerms().trim() : "" );
                rs = pstmt.executeQuery();
                int cnt = 0;
                if( rs.next() )
                {
                    cnt = rs.getInt ( "CNT" );
                }
                if ( cnt == 0 ) //pay terms not present..need to insert
                {
                    pstmt.close();
                    rs.close();
                    pstmt = conn.prepareStatement ( "INSERT INTO SHIPPINGTERMS( SHIPPINGTERMS ) VALUES ( ? )" );
                    pstmt.setString ( 1, purchaseOrderForm.getNewPayTerms() != null ? purchaseOrderForm.getNewPayTerms().trim() : "" );

                    pstmt.executeUpdate();
                }
                purchaseOrderForm.setPayTerms( purchaseOrderForm.getNewPayTerms() != null ? purchaseOrderForm.getNewPayTerms().trim() : "" );

                pstmt.close();
                rs.close();
            }
            if( purchaseOrderForm.getPurchaseOrder_Id() == -1 )
            {
            	// Need not save a copy of the Shipping Address. As it carries the Shipping address of the Open PO
            	if( purchaseOrderForm.getOpenPurchaseOrder_Id() == -1  )
            	{
					setPurchaseOrderShippingAddress( null, purchaseOrderForm );
            	}
                pstmt = conn.prepareStatement( "INSERT INTO PurchaseOrder_Table( PurchaseOrder_No, PurchaseOrder_Type, Vendor_Id, PO_Date, VendorQuote, Customer_Id, OpenPO_Id, PO_Shipping_Address_Id, PO_Billing_Address_ID, PO_Comments, PO_Status, PO_SubTotal, PO_Frieght, PO_Trade_Discount, PO_Miscellaneous, PO_Tax, PO_Total, PO_Created_By, PO_Last_Modified_On, PO_Last_Modifed_By, PO_revision_No, PO_Tax_Type, SHIPPING_METHOD_ID, PAY_TERMS, PO_Valid_To ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );

                pstmt.setString ( 1, purchaseOrderForm.getPurchaseOrderNumber().trim() );
                pstmt.setInt    ( 2, purchaseOrderForm.getPoType() );
                pstmt.setInt    ( 3, purchaseOrderForm.getVendor_id() );
                pstmt.setString ( 4, purchaseOrderForm.getPurchaseOrderDate().trim() );
                pstmt.setString ( 5, purchaseOrderForm.getVendorQuoteNumber().trim() );
                pstmt.setInt    ( 6, purchaseOrderForm.getCustomer_Id() );
                pstmt.setInt    ( 7, purchaseOrderForm.getOpenPurchaseOrder_Id() );
                pstmt.setInt    ( 8, purchaseOrderForm.getShippingAddress_id() );
                pstmt.setInt    ( 9,purchaseOrderForm.getBillingAddress_id() );
                pstmt.setString ( 10, purchaseOrderForm.getComments() );
                pstmt.setString ( 11, purchaseOrderForm.getStatus() != null ? purchaseOrderForm.getStatus() : Constants.PURCHASE_ORDER_STATUS_NEW );
				pstmt.setDouble ( 12, purchaseOrderForm.getSubTotal() );
                pstmt.setDouble ( 13, purchaseOrderForm.getFreight() );
                pstmt.setDouble ( 14, purchaseOrderForm.getTradeDiscount() );
                pstmt.setDouble ( 15, purchaseOrderForm.getMiscellaneous() );
                pstmt.setDouble ( 16, purchaseOrderForm.getTax() );
                pstmt.setDouble ( 17, purchaseOrderForm.getTotal() );
                pstmt.setString ( 18, userName );
                pstmt.setString ( 19, StringUtils.formatDateToString( date, "MM/dd/yyyy" ) );             //Last Edited Date
                pstmt.setString ( 20, userName );
                pstmt.setInt    ( 21, 1);           //First Time Revision Number always be 1.
                pstmt.setString ( 22, purchaseOrderForm.getPoTaxType() );
                pstmt.setInt    ( 23, purchaseOrderForm.getShippingMethodId() );
                pstmt.setString ( 24, purchaseOrderForm.getPayTerms() );
                pstmt.setString ( 25, purchaseOrderForm.getValidToDate() );


                pstmt.execute();
                pstmt.close();

                pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as PurchaseOrder_Id" );
                rs = pstmt.executeQuery();

                if ( rs.next() != false )
                {
                    purchaseOrderId =  rs.getInt ("PurchaseOrder_Id");
                    purchaseOrderForm.setPurchaseOrder_Id( purchaseOrderId );
                }
                rs.close();
                pstmt.close();
                pstmt = null;

                lineItemIterator    = purchaseOrderForm.getPurchaseOrderLineItemList().iterator();
                while ( lineItemIterator.hasNext() )
                {
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}

					pstmt = conn.prepareStatement( "INSERT INTO PurchaseOrder_Line_Item_Table( PurchaseOrder_Id, LineItem_no, ProcessStep_Id, ProcessType, Vendor_Part_number, Description, PackageType, Requested_date, Promised_Date, Requested_Qty, Received_Qty, UnitCost, Extended_Cost, Status, Is_cancel, PO_Finance_Hold, PO_Operation_Hold ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
                    PurchaseOrderLineItem purchaseOrderLineItem = ( PurchaseOrderLineItem ) lineItemIterator.next();

                    if ( purchaseOrderLineItem.getCancel() )
                    {
                        purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_CANCELLED );
                    }
                    else if ( purchaseOrderLineItem.getRequestedQuantity() == purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getShippedQty() )
                    {
                        purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_CLOSED );
                    }
                    else if( purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getShippedQty() > 0 )
                    {
                        purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_RECEIVED );
                    }
                    else if ( purchaseOrderLineItem.getFinanceHold() == 0 && purchaseOrderLineItem.getOperationsHold() == 0 )
                    {
                        purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_RELEASED );
                    }
                    else
                    {
						purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_NEW );
                    }


                    pstmt.setInt    ( 1,  purchaseOrderId );
                    pstmt.setInt    ( 2,  purchaseOrderLineItem.getPurchaseOrderLineItemNumber() );
                    pstmt.setInt    ( 3,  purchaseOrderLineItem.getProcessStepId() );
                    pstmt.setString ( 4,  purchaseOrderLineItem.getProcessName() );
                    pstmt.setString ( 5,  purchaseOrderLineItem.getCustomerPartNumber() );
                    pstmt.setString ( 6,  purchaseOrderLineItem.getDescription() );
                    pstmt.setString ( 7,  purchaseOrderLineItem.getPackageType() );
                    pstmt.setDate   ( 8,  StringUtils.getSQLDate(purchaseOrderLineItem.getRequestedDate(),null) );
                    pstmt.setDate   ( 9,  StringUtils.getSQLDate(purchaseOrderLineItem.getPromisedDate(),null) );
                    pstmt.setLong   ( 10,  purchaseOrderLineItem.getRequestedQuantity() );
                    pstmt.setLong   ( 11, purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getShippedQty() );
                    pstmt.setDouble ( 12, purchaseOrderLineItem.getUnitCost() );
                    pstmt.setDouble ( 13, purchaseOrderLineItem.getExtendedCost() );
                    pstmt.setString ( 14, purchaseOrderLineItem.getStatus() );
                    pstmt.setInt    ( 15, purchaseOrderLineItem.getCancel() ? 0 : 1 );
                    pstmt.setInt    ( 16, purchaseOrderLineItem.getFinanceHold() );
                    pstmt.setInt    ( 17, purchaseOrderLineItem.getOperationsHold() );

                    pstmt.execute();
					pstmt.close();
					pstmt = null;

					pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as PurchaseOrder_LineItem_Id" );
					rs = pstmt.executeQuery();

					if ( rs.next() != false )
					{
						purchaseOrderLineItemId =  rs.getInt ("PurchaseOrder_LineItem_Id");
						purchaseOrderLineItem.setPurchaseOrderLineItem_Id( purchaseOrderLineItemId );
					}
					rs.close();
					rs = null;
                }

                if( pstmt != null )
                {
					pstmt.close();
					pstmt = null;
                }

                pstmt = conn.prepareStatement( "INSERT INTO PurchaseOrder_Audit_Trail( PurchaseOrder_ID, [User_Id], Actions, TimeStamp ) VALUES ( ?, ?, ?, CURRENT_TIMESTAMP )" );
				pstmt.setInt    ( 1, purchaseOrderForm.getPurchaseOrder_Id() );
				pstmt.setString ( 2, userName );
				pstmt.setString ( 3, "<br> Created new purchase order" );

				pstmt.executeUpdate();
            }
            else
            {
				revesionNumber = fetchRevisionNumber( dbTransaction, purchaseOrderForm, userName );
                purchaseOrderForm.setRevisionNo( revesionNumber );

                pstmt = conn.prepareStatement( "UPDATE PurchaseOrder_Table SET PO_Date = ?, VendorQuote = ?, PO_Comments = ?, PO_Status = ?, PO_SubTotal = ?, PO_Frieght = ?, PO_Trade_Discount = ?, PO_Miscellaneous = ?, PO_Tax = ?, PO_Total = ?, PO_Last_Modified_On = ?, PO_Last_Modifed_By = ?, PO_revision_No = ?, PO_Tax_Type = ?, SHIPPING_METHOD_ID = ?, PAY_TERMS = ?, PO_Valid_To = ? WHERE PurchaseOrder_Id = ?" );

                pstmt.setString ( 1, purchaseOrderForm.getPurchaseOrderDate().trim() );
                pstmt.setString ( 2, purchaseOrderForm.getVendorQuoteNumber().trim() );
                pstmt.setString ( 3, purchaseOrderForm.getComments() );
                pstmt.setString ( 4, purchaseOrderForm.getStatus() );
                pstmt.setDouble ( 5, purchaseOrderForm.getSubTotal() );
                pstmt.setDouble ( 6, purchaseOrderForm.getFreight() );
                pstmt.setDouble ( 7, purchaseOrderForm.getTradeDiscount() );
                pstmt.setDouble ( 8, purchaseOrderForm.getMiscellaneous() );
                pstmt.setDouble ( 9, purchaseOrderForm.getTax() );
                pstmt.setDouble ( 10, purchaseOrderForm.getTotal() );
                pstmt.setString ( 11, StringUtils.formatDateToString( date, "MM/dd/yyyy" ) );
                pstmt.setString ( 12, userName );
                pstmt.setInt    ( 13, purchaseOrderForm.getRevisionNo() );
                pstmt.setString ( 14, purchaseOrderForm.getPoTaxType() );
                pstmt.setInt    ( 15, purchaseOrderForm.getShippingMethodId() );
                pstmt.setString ( 16, purchaseOrderForm.getPayTerms() );
                pstmt.setString ( 17, purchaseOrderForm.getValidToDate() );
                pstmt.setInt    ( 18, purchaseOrderForm.getPurchaseOrder_Id() );


                pstmt.executeUpdate();

                lineItemIterator    = purchaseOrderForm.getPurchaseOrderLineItemList().iterator();

                while ( lineItemIterator.hasNext() )
                {
                    if ( pstmt != null )
                    {
                        pstmt.close();
                        pstmt = null;
                    }

                    PurchaseOrderLineItem purchaseOrderLineItem = ( PurchaseOrderLineItem ) lineItemIterator.next();

                    boolean prevCancel = purchaseOrderLineItem.isPrevCancel();
                    boolean cancel = purchaseOrderLineItem.getCancel();

                    // added by murali krishna, on 29/3/05, statusIsRecOrClo is true if the status is set to either Received or Closed
                    boolean statusIsRecOrClo = false;

                    purchaseOrderLineItem.setStatus( PURCHASE_ORDER_STATUS_NEW );

                    if( purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getShippedQty() > 0 )
                    {
                        purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_RECEIVED );
                        statusIsRecOrClo = true;
                    }
                    if ( purchaseOrderLineItem.getRequestedQuantity() == purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getShippedQty() )
                    {
                        purchaseOrderLineItem.setStatus( Constants.PURCHASE_ORDER_STATUS_CLOSED );
                        statusIsRecOrClo = true;
                    }
                    if( cancel )
                    {
                        if ( prevCancel)
                        {
                            if( purchaseOrderLineItem.getFinanceHold() == 0 && purchaseOrderLineItem.getOperationsHold() == 0 )
                            {
                                purchaseOrderLineItem.setStatus( PURCHASE_ORDER_STATUS_CANCELLED );
                            }
                        }
                        else
                        {
                            purchaseOrderLineItem.setFinanceHold( -1);
                            purchaseOrderLineItem.setOperationsHold( -1);
                            purchaseOrderLineItem.setStatus( PURCHASE_ORDER_STATUS_NEW );
                        }
                    }
                    else
                    {
                        if( !statusIsRecOrClo && !prevCancel && ( purchaseOrderLineItem.getFinanceHold() == 0 && purchaseOrderLineItem.getOperationsHold() ==0 ) )
                        {
                            purchaseOrderLineItem.setStatus( PURCHASE_ORDER_STATUS_RELEASED);
                        }
                    }

                    if ( purchaseOrderLineItem.getPurchaseOrderLineItem_Id() != -1 )
                    {
                        pstmt = conn.prepareStatement( "UPDATE PurchaseOrder_Line_Item_Table SET ProcessStep_Id = ?, ProcessType = ?, Description = ?, PackageType = ?, Requested_date = ?, Promised_Date = ?, Requested_Qty = ?, Received_Qty = ?, UnitCost = ?, Extended_Cost = ?, Status = ?, Is_cancel =?, PO_Finance_Hold = ?, PO_Operation_Hold = ? WHERE POLineItem_Id = ?" );
                        pstmt.setInt    ( 1,  purchaseOrderLineItem.getProcessStepId() );
                        pstmt.setString ( 2,  purchaseOrderLineItem.getProcessName() );
                        pstmt.setString ( 3,  purchaseOrderLineItem.getDescription() );
                        pstmt.setString ( 4,  purchaseOrderLineItem.getPackageType() );
                        pstmt.setDate   ( 5,  StringUtils.getSQLDate(purchaseOrderLineItem.getRequestedDate(),null) );
                        pstmt.setDate   ( 6,  StringUtils.getSQLDate(purchaseOrderLineItem.getPromisedDate(),null) );
                        pstmt.setLong   ( 7,  purchaseOrderLineItem.getRequestedQuantity() );
                        pstmt.setLong   ( 8,  purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getShippedQty() );
                        pstmt.setDouble ( 9,  purchaseOrderLineItem.getUnitCost() );
                        pstmt.setDouble ( 10, purchaseOrderLineItem.getExtendedCost() );
                        pstmt.setString ( 11, purchaseOrderLineItem.getStatus() );
                        pstmt.setInt    ( 12, purchaseOrderLineItem.getCancel() ? 0 : 1 );
                        pstmt.setInt    ( 13, purchaseOrderLineItem.getFinanceHold() );
                        pstmt.setInt    ( 14, purchaseOrderLineItem.getOperationsHold() );
                        pstmt.setInt    ( 15, purchaseOrderLineItem.getPurchaseOrderLineItem_Id() );

                        pstmt.executeUpdate();
                    }
                    else
                    {
                        pstmt = conn.prepareStatement( "INSERT INTO PurchaseOrder_Line_Item_Table( PurchaseOrder_Id, LineItem_no, ProcessStep_Id, ProcessType, Description, PackageType, Requested_date, Promised_Date, Requested_Qty, Received_Qty, UnitCost, Extended_Cost, Status, Is_cancel, PO_Finance_Hold, PO_Operation_Hold ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );

                        pstmt.setInt    ( 1,  purchaseOrderForm.getPurchaseOrder_Id() );
                        pstmt.setInt    ( 2,  purchaseOrderLineItem.getPurchaseOrderLineItemNumber() );
                        pstmt.setInt    ( 3,  purchaseOrderLineItem.getProcessStepId() );
                        pstmt.setString ( 4,  purchaseOrderLineItem.getProcessName() );
                        pstmt.setString ( 5,  purchaseOrderLineItem.getDescription() );
                        pstmt.setString ( 6,  purchaseOrderLineItem.getPackageType() );
                        pstmt.setDate ( 7,  StringUtils.getSQLDate(purchaseOrderLineItem.getRequestedDate(),null) );
                        pstmt.setDate ( 8,  StringUtils.getSQLDate(purchaseOrderLineItem.getPromisedDate(),null) );
                        pstmt.setLong   ( 9,  purchaseOrderLineItem.getPreviouslyShipppedQty() + purchaseOrderLineItem.getRequestedQuantity() );
                        pstmt.setLong   ( 10, purchaseOrderLineItem.getShippedQty() );
                        pstmt.setDouble ( 11, purchaseOrderLineItem.getUnitCost() );
                        pstmt.setDouble ( 12, purchaseOrderLineItem.getExtendedCost() );
                        pstmt.setString ( 13, purchaseOrderLineItem.getStatus() );
                        pstmt.setInt    ( 14, purchaseOrderLineItem.getCancel() ? 0 : 1 );
                        pstmt.setInt    ( 15, purchaseOrderLineItem.getFinanceHold() );
                        pstmt.setInt    ( 16, purchaseOrderLineItem.getOperationsHold() );

                        pstmt.execute();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as PurchaseOrder_LineItem_Id" );
                        rs = pstmt.executeQuery();

                        if ( rs.next() != false )
                        {
                            purchaseOrderLineItemId =  rs.getInt ("PurchaseOrder_LineItem_Id");
                            purchaseOrderLineItem.setPurchaseOrderLineItem_Id( purchaseOrderLineItemId );
                        }
                        rs.close();
                        rs = null;
                    }
                }

                if ( pstmt != null )
                {
                    pstmt.close();
                    pstmt = null;
                }

				/*
                //For Deleting the Lots that exists for the Purchase Order with Aid of PurchaseOrder Id.
                pstmt = conn.prepareCall( "DELETE FROM PurchaseOrder_Lot_Number_Table WHERE PurchaseOrder_Id = ?" );
                pstmt.setInt( 1, purchaseOrderForm.getPurchaseOrder_Id() );
                pstmt.executeUpdate();
                pstmt.close();

                pstmt = conn.prepareCall( "INSERT INTO PurchaseOrder_Lot_Number_Table( PurchaseOrder_Id, LineItem_No, Lot_Number, Qty_In, Qty_Out ) VALUES ( ?, ?, ?, ?, ? )" );

                PurchaseOrderLineItem purchaseOrderLineItem = null;
                PurchaseOrderLineItemLotDataEntry opLineItemLotDataEntry = null;

                lineItemIterator    = purchaseOrderForm.getPurchaseOrderLineItemList().iterator();
                while ( lineItemIterator.hasNext() )
                {
                    purchaseOrderLineItem = ( PurchaseOrderLineItem ) lineItemIterator.next();
                    lotIterator    = purchaseOrderLineItem.getPurchaseOrderLineItemLotDataEntryList().iterator();
                    while ( lotIterator.hasNext() )
                    {
                        opLineItemLotDataEntry = ( PurchaseOrderLineItemLotDataEntry ) lotIterator.next();

                        if ( opLineItemLotDataEntry.getLotNumber() != null && opLineItemLotDataEntry.getLotNumber().trim().length() > 0 )
                        {
                            pstmt.setInt    ( 1,  purchaseOrderForm.getPurchaseOrder_Id() );
                            pstmt.setInt    ( 2,  purchaseOrderLineItem.getPurchaseOrderLineItemNumber() );
                            pstmt.setString ( 3,  opLineItemLotDataEntry.getLotNumber() );
                            pstmt.setInt    ( 4, opLineItemLotDataEntry.getQtyIn() );
                            pstmt.setInt    ( 5, opLineItemLotDataEntry.getQtyOut() );
                            pstmt.execute();
                        }
                    }
                }*/
            }

			if ( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}

            //Setting the GP Part Number for Each Purchase Order Line Item.
            pstmt = conn.prepareStatement( "SELECT dbo.fn_ct_get_gpItemNumberByProcessStepID ( ? ) AS GP_PARTNUMBER" );
            lineItemIterator    = purchaseOrderForm.getPurchaseOrderLineItemList().iterator();
            while ( lineItemIterator.hasNext() )
            {
                PurchaseOrderLineItem purchaseOrderLineItem = ( PurchaseOrderLineItem ) lineItemIterator.next();
                pstmt.setInt( 1, purchaseOrderLineItem.getProcessStepId() );
                rs = pstmt.executeQuery();

                if ( rs.next() )
                {
                    purchaseOrderLineItem.setPartNumber( rs.getString("GP_PARTNUMBER"));
                }
            }

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

			//For Updating the Purchase Order Status.
			pstmt = conn.prepareStatement( "{ call sp_change_purchase_order_status ( ? ) }" );
			pstmt.setInt ( 1, purchaseOrderForm.getPurchaseOrder_Id() );
			pstmt.execute();

            if ( dbTransaction == null )
            {
                conn.commit();
            }
            else
            {
                dbTransaction.commit();
            }
            success = true;
        }
        catch ( Throwable th )
        {
            Logger.error( "Error while saving Purchase Order Information to the database", th );
            throw new ScalarException( "Could not Save Purchase Order Information to the database", th );
        }
		finally
		{
			try
			{
				if( dbTransaction == null && conn != null )
				{
                    if( !success )
                    {
                        conn.rollback();
                    }
				}
				else
				{
					try
					{
						if( !success )
						{
							dbTransaction.rollback();
						}
					}
					catch( DBXException e )
					{
						e.printStackTrace();
					}
				}
			}
			catch (SQLException ignore) {}

			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the record set in Save Purchase Order" );
				}
			}

			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Save Purchase Order" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Save Purchase Order" );
				}
			}
		}
    }

	public PurchaseOrderForm loadPurchaseOrder( PurchaseOrderForm purchaseOrderForm, boolean loadCancelledPurchaseOrder ) throws ScalarException
    {
        Connection conn                  = null;
        PreparedStatement pstmt          = null;
        ResultSet rs                     = null;

        Iterator lineItemIterator   = null;

        try
        {
            conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );

            pstmt = conn.prepareStatement( "SELECT PurchaseOrder_No, PurchaseOrder_Type, Vendor_Id, CONVERT( VARCHAR, PO_Date, 101) AS PO_Date, PO_MAX_VALUE, VendorQuote, Customer_Id, OpenPO_Id, PO_Shipping_Address_Id, PO_Billing_Address_Id, PO_Comments, PO_Status, PO_SubTotal, PO_Frieght, PO_Trade_Discount, PO_Miscellaneous, PO_Tax, PO_Total, PO_revision_No, ISNULL( ( Select PurchaseOrder_No FROM PurchaseOrder_Table Where PurchaseOrder_Id = PO.OpenPO_Id ), '') AS OpenPONumber, PO_Tax_Type, SHIPPING_METHOD_ID, PAY_TERMS, (SELECT SHIPPING_METHOD + '   #   ' + SHIPPING_ACCOUNT FROM SHIPPING_METHODS_TABLE WHERE SHIPPING_METHOD_ID = PO.SHIPPING_METHOD_ID) AS SHIP_METHOD, CONVERT( VARCHAR, PO_Valid_To, 101) as PO_Valid_To FROM PurchaseOrder_Table PO WHERE PurchaseOrder_Id = ?" );

            pstmt.setInt( 1, purchaseOrderForm.getPurchaseOrder_Id() );
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                purchaseOrderForm.setPurchaseOrderNumber(   rs.getString( "PurchaseOrder_No" ) );
                purchaseOrderForm.setPoType(                rs.getInt( "PurchaseOrder_Type" ) );
                purchaseOrderForm.setVendor_id(             rs.getInt( "Vendor_Id" ) );
                purchaseOrderForm.setPurchaseOrderDate(     rs.getString( "PO_Date" ) );
                purchaseOrderForm.setPoMaxAmt (             rs.getDouble( "PO_MAX_VALUE" ) );
                purchaseOrderForm.setVendorQuoteNumber(     rs.getString( "VendorQuote" ) );
                purchaseOrderForm.setCustomer_Id(           rs.getInt( "Customer_Id" ) );
                purchaseOrderForm.setOpenPurchaseOrder_Id(  rs.getInt( "OpenPO_Id" ) );
                purchaseOrderForm.setShippingAddress_id(    rs.getInt( "PO_Shipping_Address_Id" ) );
				purchaseOrderForm.setBillingAddress_id(     rs.getInt( "PO_Billing_Address_Id" ) );
                purchaseOrderForm.setComments(              rs.getString( "PO_Comments" ) );
                purchaseOrderForm.setStatus(                rs.getString( "PO_Status" ) );
				purchaseOrderForm.setSubTotal(				rs.getDouble( "PO_SubTotal" ) );
                purchaseOrderForm.setFreight(               rs.getDouble( "PO_Frieght" ) );
                purchaseOrderForm.setTradeDiscount(         rs.getDouble( "PO_Trade_Discount" ) );
                purchaseOrderForm.setMiscellaneous(         rs.getDouble( "PO_Miscellaneous" ) );
                purchaseOrderForm.setTax(                   rs.getDouble( "PO_Tax" ) );
                purchaseOrderForm.setTotal(                 rs.getDouble( "PO_Total" ) );
                purchaseOrderForm.setRevisionNo(            rs.getInt( "PO_revision_No" ) );
				purchaseOrderForm.setOpenPurchaseOrderNumber( rs.getString( "OpenPONumber" ) );
                purchaseOrderForm.setPoTaxType(             rs.getString( "PO_Tax_Type" ) != null ? rs.getString( "PO_Tax_Type" ) : 0+"" );
                purchaseOrderForm.setShippingMethodId(      rs.getInt ( "SHIPPING_METHOD_ID" ) );
                purchaseOrderForm.setPayTerms(              rs.getString ( "PAY_TERMS" ) );
                purchaseOrderForm.setShipMethod(            rs.getString ( "SHIP_METHOD" ) );
                purchaseOrderForm.setValidToDate(           rs.getString( "PO_Valid_To" ) );

            }

            rs.close();
			pstmt.close();

			pstmt = null;

            pstmt = conn.prepareStatement( "SELECT SUM(PR_Total) AS PREV_RECEIPTS_TOTAL FROM PurchaseOrder_Receipt_Table PR WHERE PR.PURCHASEORDER_ID = ? AND PR.PR_STATUS != 'Cancelled'" );
			pstmt.setInt( 1, purchaseOrderForm.getPurchaseOrder_Id() );
			rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                purchaseOrderForm.setPrevReceiptsTotal ( rs.getDouble("PREV_RECEIPTS_TOTAL") );
            }

            rs.close();
			pstmt.close();

			pstmt = null;

            String sqlQuery = "SELECT POLineItem_Id, LineItem_no, PurchaseOrder_Line_Item_Table.ProcessStep_Id, Part_Number, ProcessType, Vendor_Part_Number, Description, PackageType, CONVERT( VARCHAR, Requested_date, 101) AS Requested_date, CONVERT( VARCHAR, Promised_Date, 101) AS Promised_Date, Requested_Qty, Received_Qty, UnitCost, Extended_Cost, Status, Is_Cancel, PO_Finance_Hold, PO_Operation_Hold  FROM PurchaseOrder_Line_Item_Table, Product WHERE PurchaseOrder_Line_Item_Table.ProcessStep_Id = Product.ProcessStep_Id AND PurchaseOrder_Id  = ?";

            if( loadCancelledPurchaseOrder )
            {
                sqlQuery = sqlQuery + " and Status = 'Cancelled'";
            }
            sqlQuery = sqlQuery + " ORDER BY LINEITEM_NO";
            pstmt = conn.prepareStatement( sqlQuery );

			pstmt.setInt( 1, purchaseOrderForm.getPurchaseOrder_Id() );
            rs = pstmt.executeQuery();

            PurchaseOrderLineItemList lineItemList = new PurchaseOrderLineItemList();
            PurchaseOrderLineItem purchaseOrderLineItem = null;

            while ( rs.next() )
            {
                purchaseOrderLineItem = new PurchaseOrderLineItem();

                purchaseOrderLineItem.setPurchaseOrderLineItem_Id(      rs.getInt   ( "POLineItem_Id" ) );
                purchaseOrderLineItem.setPurchaseOrderLineItemNumber(   rs.getInt   ( "LineItem_no" ) );
                purchaseOrderLineItem.setProcessStepId(                 rs.getInt   ( "ProcessStep_Id" ) ) ;
                purchaseOrderLineItem.setPartNumber(                    rs.getString( "Part_Number" ) ) ;
                purchaseOrderLineItem.setProcessName(                   rs.getString( "ProcessType" ) );
                purchaseOrderLineItem.setCustomerPartNumber(            rs.getString( "Vendor_Part_Number" ) );
                purchaseOrderLineItem.setDescription(                   rs.getString( "Description" ) );
                purchaseOrderLineItem.setPackageType(                   rs.getString( "PackageType" ) );
                purchaseOrderLineItem.setRequestedDate(                 rs.getString( "Requested_date" ) );
                purchaseOrderLineItem.setPromisedDate(                  rs.getString( "Promised_Date" ) );
                purchaseOrderLineItem.setRequestedQuantity(             rs.getLong  ( "Requested_Qty" ) );
                purchaseOrderLineItem.setPreviouslyShipppedQty(         rs.getLong  ( "Received_Qty" ) );
                purchaseOrderLineItem.setUnitCost(                      rs.getDouble  ( "UnitCost" ) );
                purchaseOrderLineItem.setExtendedCost(                  rs.getDouble  ( "Extended_Cost" ) );
                purchaseOrderLineItem.setStatus(                        rs.getString( "Status" ) );
                purchaseOrderLineItem.setCancel(                        rs.getInt   ( "Is_Cancel" ) == 0 ? true : false );
                purchaseOrderLineItem.setPrevCancel( purchaseOrderLineItem.getCancel() );
                purchaseOrderLineItem.setFinanceHold(                   rs.getInt   ( "PO_Finance_Hold") );
                purchaseOrderLineItem.setOperationsHold(                rs.getInt   ( "PO_Operation_Hold") );

                lineItemList.add( purchaseOrderLineItem );
            }
            purchaseOrderForm.setPurchaseOrderLineItemList( lineItemList );

            rs.close();
			pstmt.close();
			pstmt = null;

            purchaseOrderForm.setNewShippingMethod( null );
            purchaseOrderForm.setNewShippingAccount( null );
            purchaseOrderForm.setNewPayTerms( null );

            int poType = purchaseOrderForm.getPoType();
            if( poType == 1 )
            {
                purchaseOrderForm.setPoTypeLabel( "Open PO" );
            }
            else if( poType == 2 )
            {
                purchaseOrderForm.setPoTypeLabel( "Standard" );
            }
            else
            {
                purchaseOrderForm.setPoTypeLabel( "DropShip" );
            }
        }

        catch ( Throwable th )
        {
            Logger.error( "Error while loading Purchase Order information from database", th );
            throw new ScalarException( "Could not get purchase Order Information from database", th );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the result set in Load purchase Order " );
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the statement in Load purchase Order" );
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( Throwable th )
                {
                    Logger.error( "Error while closing the connection in Load purchase Order " );
                }
            }
        }
        return purchaseOrderForm;
    }

	/*
	 * Shows related POS to the Open PO
	 */
	public ArrayList loadRelatedPos(int purchaseOrder_Id) throws ScalarException
	{
		ArrayList purchaseOrderList = new ArrayList();
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;
		PurchaseOrder purchaseOrder = null;

		try
		{

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareCall( "SELECT PurchaseOrder_Id, PurchaseOrder_No, CONVERT( CHAR, PO_DATE, 101 )AS PO_Date, ISNULL( PO_Total, 0) as PO_Total FROM PurchaseOrder_Table Where OpenPO_Id = ?" );
			pstmt.setInt( 1, purchaseOrder_Id );
			rs 		= pstmt.executeQuery();

			while( rs.next() )
			{
				purchaseOrder = new PurchaseOrder();
				purchaseOrder.setPurchaseOrder_Id( rs.getInt( "PurchaseOrder_Id" ) );
				purchaseOrder.setPurchaseOrderNumber( rs.getString( "PurchaseOrder_No" ) );
				purchaseOrder.setPurchaseOrderDate( rs.getString( "PO_Date" ) );
				purchaseOrder.setPoMaxAmt( rs.getString( "PO_Total" ) );
				purchaseOrderList.add( purchaseOrder );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Related Purchase Orders", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Related Purchase Orders" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Related Purchase Orders" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Related Purchase Orders" );
				}
			}
		}

		return purchaseOrderList;
	}

	/*
	 * returns Audit Trail Information of the Purchase Order
	 */
	public ArrayList loadPOAuditTrail(int purchaseOrder_Id) throws ScalarException
	{
		ArrayList auditTrailList 	= new ArrayList();
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;
		SalesAuditTrail audit	= null;

		try
		{

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareStatement( "select [User_ID], Actions, [TimeStamp] from purchaseOrder_Audit_Trail where PurchaseOrder_Id = ? Order By Audit_Id" );
			pstmt.setInt( 1, purchaseOrder_Id );
			rs 		= pstmt.executeQuery();

			while( rs.next() )
			{
				audit = new SalesAuditTrail();
				audit.setUserId( rs.getString( "User_ID" ) );
				audit.setActions( rs.getString( "Actions" ) );
				audit.setUpdatedDateAsString( rs.getString( "TimeStamp" ) );
				auditTrailList.add( audit );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Audit Trail", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Audit Trail" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Audit Trail" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Audit Trail" );
				}
			}
		}

		return auditTrailList;
	}

	/*
	 * Returns all the Shipping Address Contact Persons
	 */
	public ArrayList loadVendorShippingAddresses(int vendor_id) throws ScalarException
	{
		ArrayList shippingAddressList = new ArrayList();
		Connection conn         = null;
		CallableStatement cstmt = null;
		ResultSet rs            = null;
		Address address 		= null;

		try
		{

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			cstmt 	= conn.prepareCall( "{ call sp_load_vendor_shipping_addresses( ? ) }" );
			cstmt.setInt( 1, vendor_id );
			rs 		= cstmt.executeQuery();

			while( rs.next() )
			{
				address = new Address();
				address.setAddressId( rs.getInt( "Address_ID" ) );
				address.setAddress1( rs.getString( "Address_1" ) );
				address.setAddress2( rs.getString( "Address_2" ) );
				address.setCity( rs.getString( "City" ) );
				address.setState( rs.getString( "State" ) );
				address.setZipCode( rs.getString( "Zipcode" ) );
				address.setCountry( rs.getString( "Country" ) );
				address.setPhoneNumber( rs.getString( "Phone_Number" ) );
				address.setFaxNumber( rs.getString( "Fax_Number" ) );
				address.setEmail( rs.getString( "Email" ) );
				address.setContactPerson( rs.getString( "Contact_Person" ) );
				address.setCellNumber( rs.getString( "Cell_Number" ) );
				address.setAddress3( rs.getString( "Address_3" ) );
				address.setAddress4( rs.getString( "Address_4" ) );
				address.setDefaultAddress( rs.getString( "is_Default" ) );
				address.setContactPersonAddressId( rs.getInt( "ContactPersonAddress_Id" ) );
				shippingAddressList.add( address );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Shipping Address", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Shipping Address" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Shipping Address" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Shipping Address" );
				}
			}
		}
		return shippingAddressList;
	}

	/*
	 * Search Receipts.
	 */
	public ArrayList searchRecciptOrder(ReceiptSearchForm receiptSearchForm) throws ScalarException
	{
		ArrayList receiptList 		= new ArrayList();
		Connection conn         	= null;
		CallableStatement cstmt 	= null;
		ResultSet rs            	= null;
		String poNumber				= "";
		String receiptNumber		= "";
		ReceiptOrder receiptOrder	= null;

		try
		{
			if ( receiptSearchForm == null )
			{
				receiptSearchForm = new ReceiptSearchForm();
			}

			if( receiptSearchForm.getReceiptNumber() != null )
			{
				receiptNumber	= receiptSearchForm.getReceiptNumber().trim();
			}

			if( receiptSearchForm.getPoNumber() != null )
			{
				poNumber	= receiptSearchForm.getPoNumber().trim();
			}

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			cstmt 	= conn.prepareCall( "{ call sp_search_receipts( ?, ?, ?, ?, ? ) }" );

			/*System.out.println( "receipt Number "+ receiptNumber );
			System.out.println( "PO Number "+ poNumber );
			System.out.println( "VENDOR  "+ receiptSearchForm.getVendor_id() );
			System.out.println( "PART Number "+ receiptSearchForm.getProcessStep_Id() );
			System.out.println( "YEAR "+ receiptSearchForm.getYear() );*/

			cstmt.setString	( 1, receiptNumber+"%" );
			cstmt.setString	( 2, poNumber+"%" );
			cstmt.setInt   	( 3, receiptSearchForm.getVendor_id() );
			cstmt.setInt	( 4, receiptSearchForm.getProcessStep_Id() );
			cstmt.setString	( 5, receiptSearchForm.getYear() );

			rs 		= cstmt.executeQuery();

			while( rs.next() )
			{
				receiptOrder	= new ReceiptOrder();
				receiptOrder.setReceipt_Id( rs.getInt( "Receipt_Id" ) );
				receiptOrder.setReceiptNumber( rs.getString( "ReceiptNumber" ) );
				receiptOrder.setPurchaseOrder_Id( rs.getInt( "PurchaseOrder_Id" ) );
				receiptOrder.setPurchaseOrderNumber( rs.getString( "PONumber" ) );
				receiptOrder.setVendorName( rs.getString( "Vendor_Name" ) );
				receiptOrder.setReceiptDate( rs.getString( "ReceiptDate" ) );
                receiptOrder.setPoType( rs.getInt ( "PURCHASEORDER_TYPE" ) );
                receiptOrder.setStatus( rs.getString ( "PR_STATUS" ) );

                receiptList.add( receiptOrder );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Search Receipt Orders", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Search Receipt Orders" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Search Receipt Orders" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Search Receipt Orders" );
				}
			}
		}
		return receiptList;
    }

	/*
	 * Searches For the Standard PO available for Receipts
	 */
	public ArrayList searchPurchaseOrderForReceipts(ReceiptSearchForm receiptSearchForm) throws ScalarException
	{
		ArrayList purchaseOrderList	= new ArrayList();
		Connection conn         	= null;
		CallableStatement cstmt 	= null;
		ResultSet rs            	= null;
		String poNumber				= "";
		PurchaseOrder purchaseOrder	= null;

		try
		{
			if ( receiptSearchForm == null )
			{
				receiptSearchForm = new ReceiptSearchForm();
			}

			if( receiptSearchForm.getPoNumber() != null )
			{
				poNumber	= receiptSearchForm.getPoNumber().trim();
			}

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			cstmt 	= conn.prepareCall( "{ call sp_search_po_for_receipts( ?, ?, ?, ? ) }" );

			cstmt.setString	( 1, poNumber+'%' );
			cstmt.setInt   	( 2, receiptSearchForm.getVendor_id() );
			cstmt.setInt	( 3, receiptSearchForm.getProcessStep_Id() );
			cstmt.setString	( 4, receiptSearchForm.getYear() );

			rs 		= cstmt.executeQuery();

			while( rs.next() )
			{
				purchaseOrder	= new PurchaseOrder();
				purchaseOrder.setPurchaseOrder_Id( rs.getInt( "PurchaseOrder_Id" ) );
				purchaseOrder.setPurchaseOrderNumber( rs.getString( "PONumber" ) );
				purchaseOrder.setVendorName( rs.getString( "Vendor_Name" ) );
				purchaseOrder.setPurchaseOrderDate( rs.getString( "PODate" ) );
                purchaseOrder.setPurchaseOrderType( rs.getInt ( "PURCHASEORDER_TYPE" ) );
                purchaseOrderList.add( purchaseOrder );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Search Purchase Orders for Receipts", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Search Purchase Orders for Receipts" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Search Purchase Orders for Receipts" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Search Purchase Orders for Receipts" );
				}
			}
		}
		return purchaseOrderList;
    }

	/*
	 * Returns the Purchase Order Receipt Number
	 */
	public String loadAutomatedReceiptNo() throws ScalarException
	{
		Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;
		String receiptNo 	= null;
		String number 			= null;
		String finalReceiptNo 	= null;
		String receiptPrefix 		= null;
		int receiptLength = 0;
		int incrementBy 		= 1;

		try
		{
			conn  	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
			rs 		= pstmt.executeQuery();

			if( rs.next() )
			{
				receiptPrefix = rs.getString( "Auto_Receipt_Prefix" );
				receiptLength = rs.getInt( "Auto_Receipt_Length" );
			}
			rs.close();
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement( "select Receipt_id, Receipt_Number from PurchaseOrder_Receipt_Table where Receipt_id = ( select max( Receipt_id ) from PurchaseOrder_Receipt_Table )" );
			rs    = pstmt.executeQuery();

			if( rs.next() )
			{
				receiptNo = rs.getString ( "Receipt_Number" );
			}
			else
			{
				receiptNo = "0";
			}

			try
			{
				if( receiptNo != null )
				{
					int indexValue = receiptNo.indexOf( "-" );
					number = receiptNo.substring( indexValue+1 );
					String no = (Long.parseLong (number) + incrementBy )+"";
					int noLength = no.length();
					int totalLength = receiptLength - noLength ;
					if( totalLength > 0 )
					{
						for(int  i = 0; i < totalLength ; i++  )
						{
							no = "0"+no;
						}
					}

					if( receiptPrefix != null && receiptPrefix.trim().length() != 0 )
					{
						finalReceiptNo = receiptPrefix+"-"+no ;
					}
					else
					{
						finalReceiptNo = no;
					}
				}
			}
			catch (NumberFormatException nfe)
			{
				finalReceiptNo = ( rs.getInt ( "Receipt_id" ) + incrementBy )+"" ;
			}
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			throw new ScalarException ( "Could not get Automated Receipt No from database", sqlex );
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException excp) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException excp) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException excp) {}
			}
		}
		return finalReceiptNo;
	}

	/*
	 * To Check Receipt Number Exits
	 */
	public boolean isReceiptNumberExists(String receiptNumber) throws ScalarException
	{
		boolean poExists 		= false;
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			pstmt 	= conn.prepareStatement( "select Receipt_Number from PurchaseOrder_Receipt_Table where Receipt_Number = ?" );
			pstmt.setString( 1, receiptNumber );
			rs 		= pstmt.executeQuery();

			if( rs.next() )
			{
				poExists = true;
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Check Purchase Order Receipt Number Exists ", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Check Purchase Order Receipt Number Exists" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Check Purchase Order Receipt Number Exists" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Check Purchase Order Receipt Number Exists" );
				}
			}
		}
		return poExists;
	}

	/*
	 * Save Purchase Order Receipt
	 */
	public void savePurchaseOrderReceipt(PurchaseOrderReceiptForm purchaseOrderReceiptForm, String userName) throws ScalarException
	{
		Connection conn             = null;
		PreparedStatement pstmt     = null;
		CallableStatement cstmt     = null;
		ResultSet rs                = null;
		DBTransaction dbTransaction = null;

		Iterator lineItemIterator   = null;
		Iterator lotIterator        = null;
		boolean success             = false;
		int receiptId         		= -1;
		int receiptLineItemId		= 1;

		try
		{

			dbTransaction = DBTransaction.getInstance();

			if( dbTransaction != null )
			{
				dbTransaction.begin();
			}
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
            else
            {
                conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
            }
			if( purchaseOrderReceiptForm.getReceipt_id() == -1 )
			{
				pstmt = conn.prepareCall( "INSERT INTO PurchaseOrder_Receipt_Table( Receipt_Number, PurchaseOrder_Id, Vendor_Id, Receipt_date, PR_Status, PR_Sub_Total, PR_Trade_Discount, PR_Frieght, PR_Miscellaneous, PR_Tax, PR_Total, PR_Created_By, PR_Created_Date, PR_Last_Modified_On, PR_Last_Modifed_By, PR_Po_Tax_Type, PR_INVOICE_NO) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,?,?, ? )" );

				pstmt.setString ( 1, purchaseOrderReceiptForm.getReceipt_Number() );
				pstmt.setInt    ( 2, purchaseOrderReceiptForm.getPurchaseOrder_Id() );
				pstmt.setInt    ( 3, purchaseOrderReceiptForm.getVendor_id() );
				pstmt.setString ( 4, purchaseOrderReceiptForm.getReceiptDate() );
				pstmt.setString ( 5, (purchaseOrderReceiptForm.getInvoiceNumber() == null || purchaseOrderReceiptForm.getInvoiceNumber().trim().length() == 0 ) ? "New" : Constants.PURCHASE_ORDER_STATUS_CLOSED );
				pstmt.setDouble ( 6, purchaseOrderReceiptForm.getSub_total() );
				pstmt.setDouble ( 7, purchaseOrderReceiptForm.getTrade_Discount() );
				pstmt.setDouble ( 8, purchaseOrderReceiptForm.getFrieght() );
				pstmt.setDouble ( 9, purchaseOrderReceiptForm.getMiscellaneous() );
				pstmt.setDouble ( 10, purchaseOrderReceiptForm.getTax() );
				pstmt.setDouble ( 11, purchaseOrderReceiptForm.getTotal() );
				pstmt.setString ( 12, userName );
				pstmt.setString ( 13, userName );
                pstmt.setString ( 14, purchaseOrderReceiptForm.getPoTaxType());
                pstmt.setString ( 15, purchaseOrderReceiptForm.getInvoiceNumber() );

                pstmt.execute();
				pstmt.close();
                pstmt = null;

				pstmt = conn.prepareCall ( "SELECT @@IDENTITY as Receipt_Id" );
				rs = pstmt.executeQuery();

				if ( rs.next() != false )
				{
					receiptId =  rs.getInt ("Receipt_Id");
					purchaseOrderReceiptForm.setReceipt_id( receiptId );
				}
				rs.close();
                rs = null;
                pstmt.close();
				pstmt = null;
            }
            else
            {
                pstmt = conn.prepareCall( "UPDATE PurchaseOrder_Receipt_Table SET PR_Status = ?, PR_Sub_Total = ?, PR_Trade_Discount = ?, PR_Frieght = ?, PR_Miscellaneous = ?, PR_Tax = ?, PR_Total = ?, PR_Last_Modified_On = CURRENT_TIMESTAMP, PR_Last_Modifed_By = ?, PR_Po_Tax_Type = ?, PR_INVOICE_NO = ? WHERE Receipt_id = ?" );

				pstmt.setString ( 1, (purchaseOrderReceiptForm.getInvoiceNumber() == null || purchaseOrderReceiptForm.getInvoiceNumber().trim().length() == 0 ) ? "New" : Constants.PURCHASE_ORDER_STATUS_CLOSED );
				pstmt.setDouble ( 2, purchaseOrderReceiptForm.getSub_total() );
				pstmt.setDouble ( 3, purchaseOrderReceiptForm.getTrade_Discount() );
				pstmt.setDouble ( 4, purchaseOrderReceiptForm.getFrieght() );
				pstmt.setDouble ( 5, purchaseOrderReceiptForm.getMiscellaneous() );
				pstmt.setDouble ( 6, purchaseOrderReceiptForm.getTax() );
				pstmt.setDouble ( 7, purchaseOrderReceiptForm.getTotal() );
				pstmt.setString ( 8, userName );
                pstmt.setString ( 9, purchaseOrderReceiptForm.getPoTaxType());
                pstmt.setString ( 10, purchaseOrderReceiptForm.getInvoiceNumber() );
                pstmt.setInt	( 11, purchaseOrderReceiptForm.getReceipt_id() );

				pstmt.executeUpdate();
                pstmt.close();
				pstmt = null;
            }
            lineItemIterator    = purchaseOrderReceiptForm.getReceivingLineItemList().iterator();
            while ( lineItemIterator.hasNext() )
            {
                ReceivingLineItem recevingLineItem = ( ReceivingLineItem ) lineItemIterator.next();
                if( recevingLineItem.isSelected() )
                {
                    if ( recevingLineItem.getPurchaseOrderLineItem_Id() <= 0 ) //FOR OPEN PO WE NEED TO CREATE LINE ITEM IN PURCHASE ORDER
                    {
                        pstmt = conn.prepareStatement( "INSERT INTO PurchaseOrder_Line_Item_Table( PurchaseOrder_Id, LineItem_no, ProcessStep_Id, ProcessType, Description, PackageType, Requested_date, Promised_Date, Requested_Qty, Received_Qty, UnitCost, Extended_Cost, Status, Is_cancel, PO_Finance_Hold, PO_Operation_Hold ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
                        pstmt.setInt    ( 1,  purchaseOrderReceiptForm.getPurchaseOrder_Id() );
                        pstmt.setInt    ( 2,  recevingLineItem.getPurchaseOrderLineItemNumber() );
                        pstmt.setInt    ( 3,  recevingLineItem.getProcessStepId() );
                        pstmt.setString ( 4,  recevingLineItem.getProcessName() );
                        pstmt.setString ( 5,  recevingLineItem.getDescription() );
                        pstmt.setString ( 6,  recevingLineItem.getPackageType() );
                        pstmt.setDate   ( 7,  StringUtils.getSQLDate(recevingLineItem.getRequestedDate(),null) );
                        pstmt.setDate   ( 8,  null );
                        pstmt.setLong   ( 9,  recevingLineItem.getRequestedQuantity() );
                        pstmt.setLong   ( 10, recevingLineItem.getPreviouslyShipppedQty() + recevingLineItem.getShippedQty() );
                        pstmt.setDouble ( 11, recevingLineItem.getUnitCost() );
                        pstmt.setDouble ( 12, recevingLineItem.getExtendedCost() );
                        pstmt.setString ( 13, Constants.PURCHASE_ORDER_STATUS_CLOSED  );
                        pstmt.setInt    ( 14, 1 );
                        pstmt.setInt    ( 15, 0 ); //APPROVED STATE
                        pstmt.setInt    ( 16, 0 ); //APPROVED STATE

                        pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;

                        pstmt = conn.prepareCall ( "SELECT @@IDENTITY as PO_LINEITEM_ID" );
                        rs = pstmt.executeQuery();

                        if ( rs.next() != false )
                        {
                            int poLineItemId =  rs.getInt ("PO_LINEITEM_ID");
                            recevingLineItem.setPurchaseOrderLineItem_Id( poLineItemId );
                        }
                        rs.close();
                        pstmt.close();
                        pstmt = null;
                    }
                    if ( recevingLineItem.getRecevingLineItem_Id() <= 0 )
                    {
                        pstmt = conn.prepareCall( "INSERT INTO PurchaseOrder_Receipt_LineItem_Table( Receipt_id, POLineItem_Id, ProcessStep_ID, description, LineItem_Receipt_Date, UnitCost, Extended_Cost, Shipped_Qty, Invoiced_Qty )VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ? )" );

						pstmt.setInt    ( 1, purchaseOrderReceiptForm.getReceipt_id() );
						pstmt.setInt    ( 2, recevingLineItem.getPurchaseOrderLineItem_Id() );
						pstmt.setInt    ( 3, recevingLineItem.getProcessStepId() );
						pstmt.setString ( 4, recevingLineItem.getDescription() );
						pstmt.setDate ( 5, StringUtils.getSQLDate(recevingLineItem.getReceivedDate(),null) );
						pstmt.setDouble ( 6, recevingLineItem.getUnitCost() );
						pstmt.setDouble ( 7, recevingLineItem.getExtendedCost() );
						pstmt.setLong   ( 8, recevingLineItem.getShippedQty() );
						pstmt.setLong   ( 9, recevingLineItem.getShippedQty() );

						pstmt.execute();
						pstmt.close();
						pstmt = null;

						pstmt = conn.prepareCall ( "SELECT @@IDENTITY as Receipt_LineItem_Id" );
						rs = pstmt.executeQuery();

						if ( rs.next() != false )
						{
							receiptLineItemId =  rs.getInt ( "Receipt_LineItem_Id" );
							recevingLineItem.setRecevingLineItem_Id( receiptLineItemId );
						}
                        pstmt.close();
						pstmt = null;
                        rs.close();
                        rs = null;
                    }
                    else
                    {
                        pstmt = conn.prepareCall( "UPDATE PurchaseOrder_Receipt_LineItem_Table SET ProcessStep_ID = ?, description = ?, UnitCost = ?, Extended_Cost = ?, Shipped_Qty = ?, Invoiced_Qty = ?, LineItem_Receipt_Date = ?  WHERE Receipt_LineItem_id = ?" );
						pstmt.setInt    ( 1, recevingLineItem.getProcessStepId() );
						pstmt.setString ( 2, recevingLineItem.getDescription() );
						pstmt.setDouble ( 3, recevingLineItem.getUnitCost() );
						pstmt.setDouble ( 4, recevingLineItem.getExtendedCost() );
						pstmt.setLong   ( 5, recevingLineItem.getShippedQty() );
						pstmt.setLong   ( 6, recevingLineItem.getShippedQty() );
						pstmt.setDate ( 7, StringUtils.getSQLDate(recevingLineItem.getReceivedDate(), null) );
						pstmt.setInt    ( 8, recevingLineItem.getRecevingLineItem_Id());

						pstmt.executeUpdate();
                        pstmt.close();
                        pstmt = null;
                    }

                    //Resetting flag in lot_table
                    pstmt = conn.prepareCall( "UPDATE LOT_TABLE SET PO_RECEIPT_ID = 0 WHERE LOT_ID IN ( SELECT LOT_ID FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE Receipt_LineItem_id = ? )" );
					pstmt.setInt( 1, recevingLineItem.getRecevingLineItem_Id() );
					pstmt.executeUpdate();
					pstmt.close();
                    pstmt = null;

                    //For Deleting the Lots that exists for the Purchase Order with Aid of PurchaseOrder Id.
					pstmt = conn.prepareCall( "DELETE FROM PurchaseOrder_Receipt_Lot_Table WHERE Receipt_LineItem_id = ?" );
					pstmt.setInt( 1, recevingLineItem.getRecevingLineItem_Id() );
					pstmt.executeUpdate();
					pstmt.close();
                    pstmt = null;
                    //pstmt = conn.prepareCall( "INSERT INTO PurchaseOrder_Receipt_Lot_Table( Receipt_LineItem_id, Lot_Number, Qty_In, Qty_Out )VALUES( ?, ?, ?, ? )" );
                    pstmt = conn.prepareCall( "{CALL SP_INSERT_LOT_FOR_PO_RECEIPTS(?, ?, ?, ?, ?, ?, ?)}" );
                    lotIterator    		= recevingLineItem.getPurchaseOrderLineItemLotDataEntryList().iterator();
                    while ( lotIterator.hasNext() )
                    {
                        PurchaseOrderLineItemLotDataEntry opLineItemLotDataEntry = ( PurchaseOrderLineItemLotDataEntry ) lotIterator.next();

                        if ( opLineItemLotDataEntry.isSelected() && opLineItemLotDataEntry.getLotNumber() != null && opLineItemLotDataEntry.getLotNumber().trim().length() > 0 )
                        {
                            pstmt.setInt    ( 1, recevingLineItem.getRecevingLineItem_Id() );
                            pstmt.setInt    ( 2, opLineItemLotDataEntry.getLotId() );
                            pstmt.setString ( 3, opLineItemLotDataEntry.getLotNumber().trim() );
                            pstmt.setInt    ( 4, opLineItemLotDataEntry.getQtyPulled() );
                            pstmt.setInt    ( 5, purchaseOrderReceiptForm.getVendor_id() );
                            pstmt.setInt    ( 6, recevingLineItem.getProcessStepId( ) );
                            pstmt.setString ( 7, purchaseOrderReceiptForm.getPurchaseOrderNumber( ) );
                            pstmt.execute();
                        }
                    }
                    pstmt.close();
				    pstmt = null;

                    pstmt = conn.prepareStatement( "Update PurchaseOrder_Line_Item_Table SET Received_Qty = ( SELECT SUM(Shipped_Qty) FROM PurchaseOrder_Receipt_LineItem_Table WHERE POLineItem_Id = ? ) WHERE POLineItem_Id = ? " );
                    pstmt.setInt( 1, recevingLineItem.getPurchaseOrderLineItem_Id() );
                    pstmt.setInt( 2, recevingLineItem.getPurchaseOrderLineItem_Id() );
                    pstmt.execute();

                    pstmt.close();
                    pstmt = null;

                    cstmt = conn.prepareCall( "{ call sp_change_purchase_order_lineItem_status( ? ) }" );
					cstmt.setInt( 1, recevingLineItem.getPurchaseOrderLineItem_Id() );
					cstmt.executeUpdate();

                    cstmt.close();
                    cstmt = null;
                }
            }
            //}
			/*else //comment....
			{
				if( dbTransaction != null )
				{
					conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
				}
				else
				{
					conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
				}

				pstmt = conn.prepareCall( "UPDATE PurchaseOrder_Receipt_Table SET PR_Status = ?, PR_Sub_Total = ?, PR_Trade_Discount = ?, PR_Frieght = ?, PR_Miscellaneous = ?, PR_Tax = ?, PR_Total = ?, PR_Last_Modified_On = CURRENT_TIMESTAMP, PR_Last_Modifed_By = ?, PR_Po_Tax_Type = ? WHERE Receipt_id = ?" );

				pstmt.setString ( 1, "New" );
				pstmt.setDouble ( 2, purchaseOrderReceiptForm.getSub_total() );
				pstmt.setDouble ( 3, purchaseOrderReceiptForm.getTrade_Discount() );
				pstmt.setDouble ( 4, purchaseOrderReceiptForm.getFrieght() );
				pstmt.setDouble ( 5, purchaseOrderReceiptForm.getMiscellaneous() );
				pstmt.setDouble ( 6, purchaseOrderReceiptForm.getTax() );
				pstmt.setDouble ( 7, purchaseOrderReceiptForm.getTotal() );
				pstmt.setString ( 8, userName );
                pstmt.setString ( 9, purchaseOrderReceiptForm.getPoTaxType());
				pstmt.setInt	( 10, purchaseOrderReceiptForm.getReceipt_id() );

				pstmt.executeUpdate();

				lineItemIterator    = purchaseOrderReceiptForm.getReceivingLineItemList().iterator();

				while ( lineItemIterator.hasNext() )
				{
					if ( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}

					ReceivingLineItem recevingLineItem = ( ReceivingLineItem ) lineItemIterator.next();

					if ( recevingLineItem.getRecevingLineItem_Id() != -1 )
					{
						pstmt = conn.prepareCall( "UPDATE PurchaseOrder_Receipt_LineItem_Table SET ProcessStep_ID = ?, description = ?, UnitCost = ?, Extended_Cost = ?, Shipped_Qty = ?, Invoiced_Qty = ?, LineItem_Receipt_Date = ?  WHERE Receipt_LineItem_id = ?" );
						pstmt.setInt    ( 1, recevingLineItem.getProcessStepId() );
						pstmt.setString ( 2, recevingLineItem.getDescription() );
						pstmt.setDouble ( 3, recevingLineItem.getUnitCost() );
						pstmt.setDouble ( 4, recevingLineItem.getExtendedCost() );
						pstmt.setLong   ( 5, recevingLineItem.getShippedQty() );
						pstmt.setLong   ( 6, recevingLineItem.getShippedQty() );
						pstmt.setDate ( 7, StringUtils.getSQLDate(recevingLineItem.getReceivedDate(), null) );
						pstmt.setInt    ( 8, recevingLineItem.getRecevingLineItem_Id());

						pstmt.executeUpdate();
					}
					else
					{
						pstmt = conn.prepareCall( "INSERT INTO PurchaseOrder_Receipt_LineItem_Table( Receipt_id, POLineItem_Id, ProcessStep_ID, description, LineItem_Receipt_Date, UnitCost, Extended_Cost, Shipped_Qty, Invoiced_Qty )VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ? )" );

						pstmt.setInt    ( 1, receiptId );
						pstmt.setInt    ( 2, recevingLineItem.getPurchaseOrderLineItem_Id() );
						pstmt.setInt    ( 3, recevingLineItem.getProcessStepId() );
						pstmt.setString ( 4, recevingLineItem.getDescription() );
						pstmt.setDate ( 5, StringUtils.getSQLDate(recevingLineItem.getReceivedDate(),null) );
						pstmt.setDouble ( 6, recevingLineItem.getUnitCost() );
						pstmt.setDouble ( 7, recevingLineItem.getExtendedCost() );
						pstmt.setLong   ( 8, recevingLineItem.getShippedQty() );
						pstmt.setLong   ( 9, recevingLineItem.getShippedQty() );
						pstmt.execute();
					}

					if ( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
				}


				ReceivingLineItem recevingLineItem = null;
				PurchaseOrderLineItemLotDataEntry opLineItemLotDataEntry = null;
				lineItemIterator    = purchaseOrderReceiptForm.getReceivingLineItemList().iterator();
				while ( lineItemIterator.hasNext() )
				{
					recevingLineItem 	= ( ReceivingLineItem ) lineItemIterator.next();

					if ( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}

					//For Deleting the Lots that exists for the Purchase Order with Aid of PurchaseOrder Id.
					pstmt = conn.prepareCall( "DELETE FROM PurchaseOrder_Receipt_Lot_Table WHERE Receipt_LineItem_id = ?" );
					pstmt.setInt( 1, recevingLineItem.getRecevingLineItem_Id() );
					pstmt.executeUpdate();
					pstmt.close();

					//pstmt = conn.prepareCall( "INSERT INTO PurchaseOrder_Receipt_Lot_Table( Receipt_LineItem_id, Lot_Number, Qty_In, Qty_Out )VALUES( ?, ?, ?, ? )" );
					pstmt = conn.prepareCall( "{CALL SP_INSERT_LOT_FOR_PO_RECEIPTS(?, ?, ?, ?, ?, ?, ?)}" );

					lotIterator 	= recevingLineItem.getPurchaseOrderLineItemLotDataEntryList().iterator();
					while ( lotIterator.hasNext() )
					{
						opLineItemLotDataEntry = ( PurchaseOrderLineItemLotDataEntry ) lotIterator.next();

						if (  opLineItemLotDataEntry.isSelected() && opLineItemLotDataEntry.getLotNumber() != null && opLineItemLotDataEntry.getLotNumber().trim().length() > 0 )
						{
                            pstmt.setInt    ( 1, recevingLineItem.getRecevingLineItem_Id() );
                            pstmt.setInt    ( 2, opLineItemLotDataEntry.getLotId() );
                            pstmt.setString ( 3, opLineItemLotDataEntry.getLotNumber().trim() );
                            pstmt.setInt    ( 4, opLineItemLotDataEntry.getQtyIn() );
                            pstmt.setInt    ( 5, purchaseOrderReceiptForm.getVendor_id() );
                            pstmt.setInt    ( 6, recevingLineItem.getProcessStepId( ) );
                            pstmt.setString ( 7, purchaseOrderReceiptForm.getPurchaseOrderNumber( ) );
							pstmt.execute();
						}
					}
				}

				pstmt.close();
				pstmt = null;

				lineItemIterator    = purchaseOrderReceiptForm.getReceivingLineItemList().iterator();

				while ( lineItemIterator.hasNext() )
				{
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}

					pstmt = conn.prepareStatement( "Update PurchaseOrder_Line_Item_Table SET Received_Qty = ( SELECT SUM(Shipped_Qty) FROM PurchaseOrder_Receipt_LineItem_Table WHERE POLineItem_Id = ? ) WHERE POLineItem_Id = ? " );
					recevingLineItem 	= ( ReceivingLineItem ) lineItemIterator.next();

					if( recevingLineItem.isSelected() )
					{
						pstmt.setInt( 1, recevingLineItem.getPurchaseOrderLineItem_Id() );
						pstmt.setInt( 2, recevingLineItem.getPurchaseOrderLineItem_Id() );
						pstmt.execute();
					}

					if( cstmt != null )
					{
						cstmt.close();
						cstmt = null;
					}

					cstmt = conn.prepareCall( "{ call sp_change_purchase_order_lineItem_status( ? ) }" );
					cstmt.setInt( 1, recevingLineItem.getPurchaseOrderLineItem_Id() );
					cstmt.executeUpdate();
				}

			}*/

			//For Updating the Purchase Order Status.
			cstmt = conn.prepareCall( "{ call sp_change_purchase_order_status ( ? ) }" );
			cstmt.setInt ( 1, purchaseOrderReceiptForm.getPurchaseOrder_Id() );
			cstmt.execute();

			success = true;
		}
		catch ( Throwable th )
		{
            th.printStackTrace();
            Logger.error( "Error while saving Purchase Order Information to the database", th );
			throw new ScalarException( "Could not Save Purchase Order Information to the database", th );
		}
		finally
		{
			try
			{
				if( dbTransaction == null )
				{
					if( conn != null )
					{
						if( success )
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				else
				{
					try
					{
						if( success )
						{
							dbTransaction.commit();

						}
						else
						{
							dbTransaction.rollback();
						}
					}
					catch( DBXException e )
					{
						e.printStackTrace();
					}
				}
			}
			catch (SQLException ignore) {}

			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the record set in Save Purchase Order Receipt" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Save Purchase Order Receipt" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Save Purchase Order Receipt" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Save Purchase Order Receipt" );
				}
			}
		}
    }

	/*
	 * Loads Purchase Order Receipts
	 */
	public void loadPurchaseOrderReceipt(PurchaseOrderReceiptForm purchaseOrderReceiptForm) throws ScalarException
	{
		Connection conn             = null;
		PreparedStatement pstmt     = null;
		ResultSet rs                = null;
		Iterator lineItemIterator	= null;

		try
		{
			conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );

			pstmt = conn.prepareStatement( "SELECT PR.Receipt_Number, PR.PurchaseOrder_Id, PO.PurchaseOrder_No, PO.PO_MAX_VALUE, PR.Vendor_Id, CONVERT( VARCHAR, PR.Receipt_date, 101) AS Receipt_date, PR.PR_Status, PR.PR_Sub_Total, PR.PR_Trade_Discount, PR.PR_Frieght, PR_Miscellaneous, PR_Tax, PR_Total, CONVERT( VARCHAR, PR_Last_Modified_On, 101) AS PR_Last_Modified_On, PR.PR_Last_Modifed_By, V.V_Name, PR.PR_Po_Tax_Type, PO.PURCHASEORDER_TYPE, PR.PR_INVOICE_NO FROM PurchaseOrder_Receipt_Table PR, PurchaseOrder_Table PO, Vendor V WHERE PR.PurchaseOrder_Id = PO.PurchaseOrder_Id AND PR.Vendor_Id = V.Vendor_Id AND PR.Receipt_Id = ?" );
			pstmt.setInt( 1, purchaseOrderReceiptForm.getReceipt_id() );

			rs = pstmt.executeQuery();

			if ( rs.next() )
			{
				purchaseOrderReceiptForm.setReceipt_Number( rs.getString( "Receipt_Number" ) );
				purchaseOrderReceiptForm.setPurchaseOrder_Id( rs.getInt( "PurchaseOrder_Id" ) );
				purchaseOrderReceiptForm.setPurchaseOrderNumber( rs.getString( "PurchaseOrder_No" ) );
                purchaseOrderReceiptForm.setPoMaxAmt( rs.getDouble( "PO_MAX_VALUE" ));
                purchaseOrderReceiptForm.setVendor_id( rs.getInt( "Vendor_Id" ) );
				purchaseOrderReceiptForm.setReceiptDate( rs.getString( "Receipt_date" ) );
				purchaseOrderReceiptForm.setStatus( rs.getString( "PR_Status" ) );
				purchaseOrderReceiptForm.setSub_total( rs.getDouble( "PR_Sub_Total" ) );
				purchaseOrderReceiptForm.setTrade_Discount( rs.getDouble( "PR_Trade_Discount" ) );
				purchaseOrderReceiptForm.setFrieght( rs.getDouble( "PR_Frieght" ) );
				purchaseOrderReceiptForm.setMiscellaneous( rs.getDouble( "PR_Miscellaneous" ) );
				purchaseOrderReceiptForm.setTax( rs.getDouble( "PR_Tax" ) );
				purchaseOrderReceiptForm.setTotal( rs.getDouble( "PR_Total" ) );
				purchaseOrderReceiptForm.setLast_modified_on( rs.getString( "PR_Last_Modified_On" ) );
				purchaseOrderReceiptForm.setLast_modified_by( rs.getString( "PR_Last_Modifed_By" ) );
				purchaseOrderReceiptForm.setVendorName( rs.getString( "V_Name" ) );
                purchaseOrderReceiptForm.setPoTaxType( rs.getString("PR_Po_Tax_Type"));
                purchaseOrderReceiptForm.setPoType( rs.getInt ( "PURCHASEORDER_TYPE" ) );
                purchaseOrderReceiptForm.setInvoiceNumber ( rs.getString ( "PR_INVOICE_NO" ) );
            }

            rs.close();
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement( "SELECT SUM(PR_Total) AS PREV_RECEIPTS_TOTAL FROM PurchaseOrder_Receipt_Table PR WHERE PR.Receipt_Id !=? AND PR.PURCHASEORDER_ID = (SELECT PURCHASEORDER_ID FROM PurchaseOrder_Receipt_Table WHERE Receipt_Id = ?) AND PR.PR_STATUS != 'Cancelled'" );
			pstmt.setInt( 1, purchaseOrderReceiptForm.getReceipt_id() );
			pstmt.setInt( 2, purchaseOrderReceiptForm.getReceipt_id() );
			rs = pstmt.executeQuery();
            if ( rs.next() )
            {
                purchaseOrderReceiptForm.setPrevReceiptsTotal ( rs.getDouble("PREV_RECEIPTS_TOTAL") );
            }

            rs.close();
			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement( "SELECT PRL.Receipt_LineItem_id, PRL.Receipt_id, PRL.POLineItem_Id, POL.LineItem_no, POL.ProcessType, POL.PackageType, PRL.ProcessStep_ID, PRL.description, CONVERT( VARCHAR, POL.Requested_Date, 101) AS Requested_Date, POL.Requested_Qty, POL.Received_Qty,CONVERT( VARCHAR, PRL.LineItem_Receipt_Date, 101) as LineItem_Receipt_Date, PRL.UnitCost, PRL.Extended_Cost, PRL.Shipped_Qty, PRL.Invoiced_Qty, P.Part_Number FROM PurchaseOrder_Receipt_LineItem_Table PRL, PurchaseOrder_Line_Item_Table POL, Product P WHERE PRL.POLineItem_Id = POL.POLineItem_Id AND PRL.ProcessStep_ID = P.ProcessStep_ID AND PRL.Receipt_Id = ?" );
			pstmt.setInt( 1, purchaseOrderReceiptForm.getReceipt_id() );
			rs = pstmt.executeQuery();

			ReceivingLineItemList receivingLineItemList = new ReceivingLineItemList();
			ReceivingLineItem receivingLineItem 		= null;

			while ( rs.next() )
			{
				receivingLineItem = new ReceivingLineItem();

				receivingLineItem.setRecevingLineItem_Id( rs.getInt( "Receipt_LineItem_id" ) );
				receivingLineItem.setPurchaseOrderLineItem_Id( rs.getInt( "POLineItem_Id" ) );
				receivingLineItem.setPurchaseOrderLineItemNumber( rs.getInt( "LineItem_no" ) );
				receivingLineItem.setProcessName( rs.getString( "ProcessType" ) );
				receivingLineItem.setPackageType( rs.getString( "PackageType" ) );
				receivingLineItem.setProcessStepId( rs.getInt( "ProcessStep_ID" ) ) ;
				receivingLineItem.setDescription( rs.getString( "description" ) );
				receivingLineItem.setRequestedDate( rs.getString( "Requested_Date" ) );
				receivingLineItem.setRequestedQuantity( rs.getLong( "Requested_Qty" ) );
				receivingLineItem.setPreviouslyShipppedQty( rs.getLong( "Received_Qty" ) );
				receivingLineItem.setReceivedDate( rs.getString( "LineItem_Receipt_Date" ) );
				receivingLineItem.setUnitCost( rs.getDouble( "UnitCost" ) );
				receivingLineItem.setExtendedCost( rs.getDouble( "Extended_Cost" ) );
				receivingLineItem.setShippedQty( rs.getLong( "Shipped_Qty" ) );
                receivingLineItem.setOrgShippedQty( receivingLineItem.getShippedQty() );
				receivingLineItem.setInvoiced_Qty( rs.getLong( "Invoiced_Qty" ) );
				receivingLineItem.setPartNumber( rs.getString( "Part_Number" ) );
				receivingLineItem.setSelected( true );

				receivingLineItemList.add( receivingLineItem );
			}
			purchaseOrderReceiptForm.setReceivingLineItemList( receivingLineItemList );

			rs.close();
			pstmt.close();
			pstmt = null;

			//pstmt = conn.prepareStatement( "SELECT Lot_Number, Qty_In, Qty_Out FROM PurchaseOrder_Receipt_Lot_Table WHERE Receipt_LineItem_id = ?" );
			pstmt = conn.prepareStatement( "SELECT PORLT.LOT_ID, PORLT.LOT_NUMBER, PORLT.QTY_IN, LT.DATE_IN, LT.LOT_CURRENT_STATUS, LT.PO_NUMBER, 0 AS IS_SELECTED, (SELECT PROCESSSTEP_ID FROM PURCHASEORDER_RECEIPT_LINEITEM_TABLE WHERE RECEIPT_LINEITEM_ID = PORLT.RECEIPT_LINEITEM_ID) AS PROCESSSTEP_ID, (SELECT PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PS_TYPE_NAME, (SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID) AS PART_NUMBER FROM PURCHASEORDER_RECEIPT_LOT_TABLE PORLT LEFT OUTER JOIN LOT_TABLE LT ON LT.LOT_ID= PORLT.LOT_ID WHERE RECEIPT_LINEITEM_ID = ?" );

			PurchaseOrderLineItemLotDataEntryList poLineItemLotDataEntryList = null;
			PurchaseOrderLineItemLotDataEntry opLineItemLotDataEntry = null;

			lineItemIterator    = purchaseOrderReceiptForm.getReceivingLineItemList().iterator();

			while ( lineItemIterator.hasNext() )
			{
				receivingLineItem = ( ReceivingLineItem ) lineItemIterator.next();
				poLineItemLotDataEntryList = new PurchaseOrderLineItemLotDataEntryList();

				pstmt.setInt( 1, receivingLineItem.getRecevingLineItem_Id() );

                rs = pstmt.executeQuery();
				while ( rs.next() )
				{
					opLineItemLotDataEntry = new PurchaseOrderLineItemLotDataEntry();

                    opLineItemLotDataEntry.setLotId( rs.getInt ( "LOT_ID" ) );
                    opLineItemLotDataEntry.setLotNumber( rs.getString ( "LOT_NUMBER" ) );
                    opLineItemLotDataEntry.setQtyIn( rs.getInt ( "QTY_IN" ) );
                    opLineItemLotDataEntry.setDateIn( StringUtils.formatDateToString( rs.getDate( "DATE_IN" ) ) );
                    opLineItemLotDataEntry.setStatus( rs.getString ( "LOT_CURRENT_STATUS" ) );
                    opLineItemLotDataEntry.setPoNumber( rs.getString ( "PO_NUMBER" ) );
                    opLineItemLotDataEntry.setSelected( rs.getInt ( "IS_SELECTED" ) == 0 );
                    opLineItemLotDataEntry.setProcessStepId( rs.getInt ( "PROCESSSTEP_ID" ) );
                    opLineItemLotDataEntry.setPsTypeName( rs.getString ( "PS_TYPE_NAME" ) );
                    opLineItemLotDataEntry.setPartNumber( rs.getString ( "PART_NUMBER" ) );
                    poLineItemLotDataEntryList.add( opLineItemLotDataEntry );
                }
                receivingLineItem.setPurchaseOrderLineItemLotDataEntryList( poLineItemLotDataEntryList );
			}
		}

		catch ( Throwable th )
		{
			Logger.error( "Error while loading Purchase Order information from database", th );
			throw new ScalarException( "Could not get purchase Order Information from database", th );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load purchase Order " );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load purchase Order" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load purchase Order " );
				}
			}
		}
	}


	/*
	 * To Fetch the Part Number Description
	 */
	public ArrayList loadPartDescription(int vendor_Id, String processType) throws ScalarException
	{
		ArrayList partNumbersList 	= new ArrayList();
		Connection conn         = null;
		PreparedStatement pstmt = null;
		ResultSet rs            = null;
		String comments			= "";
		String patternStr 		= "\"";
		String replacementStr 	= "";

		try
		{
			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			Pattern pattern = Pattern.compile(patternStr);

			pstmt 	= conn.prepareStatement( "SELECT ISNULL( P.P_COMMENTS, '') AS P_COMMENTS, P.ProcessStep_ID FROM Process_Step PS INNER JOIN Product P ON PS.ProcessStep_ID = P.ProcessStep_ID INNER JOIN Process_Step_Location_Table PSLT ON PS.ProcessStep_ID = PSLT.ProcessStep_ID INNER JOIN Location L ON PSLT.Location_ID = L.Location_ID WHERE PS.PS_Type_Name = ? AND L.Vendor_ID = ? Order By Part_number" );
			pstmt.setString( 1, processType );
			pstmt.setInt   ( 2, vendor_Id );

			rs = pstmt.executeQuery();

			while( rs.next() )
			{
				comments = rs.getString( "P_COMMENTS" );
				Matcher matcher = pattern.matcher(comments);
				String output = matcher.replaceAll(replacementStr);

				partNumbersList.add( new LabelValueBean( output, rs.getString( "ProcessStep_ID" ) ) );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Vendor Locations List", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Vendor Locations List" );
				}
			}
			if ( pstmt != null )
			{
				try
				{
					pstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Vendor Locations List" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Vendor Locations List" );
				}
			}
		}
		return partNumbersList;
    }

    public ArrayList loadPOTaxesList() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareCall(SqlQueryMappings.get ("finance.taxAndTerms.loadPOTaxes"));
            rs = pstmt.executeQuery();

            while(rs.next())
            {
                String state = rs.getString("State");
                String tax = StringUtils.formatAmount( rs.getFloat("Tax"));
                list.add( new org.apache.struts.util.LabelValueBean( state+" - "+tax, state ) );
            }
        }
        catch (Exception se)
        {
            se.printStackTrace();
        }
        finally
        {
            if(rs != null) {
                try { rs.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

       return list;
    }

    public boolean doesPOTaxStateExist (DBTransaction dbTransaction,String state) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int totalstates = 0;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("finance.purchaseOrder.doesPOTaxStateExist"));
            pstmt.setString (1, state);
            rs = pstmt.executeQuery();

            if (rs.next() )
            {
                totalstates = rs.getInt("Total");
            }
            if(totalstates > 0)
            {
                return true;
            }

            return false;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not find State, \"" + state + "\" in the database", sqle);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch ( SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    public void savePOTax(String states, String tax ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("finance.taxAndTerms.insertTaxes"));

            if(( states != null && states.length() != 0) && ( tax != null && tax.length() != 0))
            {
                pstmt.setString (1, states);
                pstmt.setFloat  (2, Float.parseFloat(tax));
            }
            pstmt.execute();
            success = true;
        }
        catch (Exception se)
        {
              se.printStackTrace();
        }
        finally
        {
           try
           {
                if (success == true)
                    conn.commit();
                else
                    conn.rollback();
           }
           catch (SQLException ignore) {}

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    /**
     * Added by Murali Krishna, gets the tax value of a state from database
     * @param state
     * @return
     * @throws ScalarException
     */
    public double getTaxValue(String state ) throws ScalarException
    {
        Connection conn 		            = null;
        PreparedStatement pstmt             = null;
        ResultSet rs 			            = null;
        double tax = 0.0f;

        try
        {
            conn  	= DataSource.getConnection();
            pstmt 	= conn.prepareStatement( "select Tax from purchaseOrder_Tax_Table where State like ?" );
            pstmt.setString(1, state );
            rs 		= pstmt.executeQuery();
            if( rs.next() )
            {
                tax = rs.getDouble(1);
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Purchase Order Tax value from database", sqlex );
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return tax;
    }


    public PurchaseOrderLineItemLotDataEntryList loadLotsForReceipts(DBTransaction dbTransaction, String poNumber, int processStepId, int vendorId, int receiptLineItemId, int receiptId ) throws ScalarException
    {
        Connection conn 		            = null;
        CallableStatement cstmt             = null;
        ResultSet rset 			            = null;
        PurchaseOrderLineItemLotDataEntryList purchaseOrderLineItemLotDataEntryList = new PurchaseOrderLineItemLotDataEntryList();
        PurchaseOrderLineItemLotDataEntry purchaseOrderLineItemLotDataEntry = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn  	= DataSource.getConnection();
            }
            cstmt 	= conn.prepareCall( "{CALL SP_LOAD_LOTS_FOR_PO_RECEIPT(?, ?, ?, ?, ?)}" );
            cstmt.setString( 1, poNumber );
            cstmt.setInt( 2, processStepId );
            cstmt.setInt( 3, vendorId );
            cstmt.setInt( 4, receiptLineItemId );
            cstmt.setInt ( 5, receiptId );
            rset 		= cstmt.executeQuery();
            while( rset.next() )
            {
                purchaseOrderLineItemLotDataEntry = new PurchaseOrderLineItemLotDataEntry();

                purchaseOrderLineItemLotDataEntry.setLotId( rset.getInt ( "LOT_ID" ) );
                purchaseOrderLineItemLotDataEntry.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                purchaseOrderLineItemLotDataEntry.setQtyIn( rset.getInt ( "QTY_IN" ) );
                purchaseOrderLineItemLotDataEntry.setQtyPulled( rset.getInt ( "QTY_Pulled" ) );
                purchaseOrderLineItemLotDataEntry.setDateIn( StringUtils.formatDateToString( rset.getDate( "DATE_IN" ) ) );
                purchaseOrderLineItemLotDataEntry.setStatus( rset.getString ( "LOT_CURRENT_STATUS" ) );
                purchaseOrderLineItemLotDataEntry.setPoNumber( rset.getString ( "PO_NUMBER" ) );
                purchaseOrderLineItemLotDataEntry.setSelected( rset.getInt ( "IS_SELECTED" ) == 0 );
                purchaseOrderLineItemLotDataEntry.setProcessStepId( rset.getInt ( "PROCESSSTEP_ID" ) );
                purchaseOrderLineItemLotDataEntry.setPsTypeName( rset.getString ( "PS_TYPE_NAME" ) );
                purchaseOrderLineItemLotDataEntry.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                purchaseOrderLineItemLotDataEntry.setReceiptLineItemId( rset.getInt ( "RECEIPT_LINEITEM_ID" ) );

                purchaseOrderLineItemLotDataEntryList.add ( purchaseOrderLineItemLotDataEntry );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Purchase Order Receipt Lot details from database", sqlex );
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); rset = null; } catch (SQLException excp) {}
            }
            if (cstmt != null)
            {
                try { cstmt.close(); cstmt = null; } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); conn = null; } catch (SQLException excp) {}
            }
        }
        return purchaseOrderLineItemLotDataEntryList;
    }

    public int getPOMaxLineItemNo( DBTransaction dbTransaction, int poId ) throws ScalarException
    {
        Connection conn 		            = null;
        PreparedStatement pstmt             = null;
        ResultSet rset 			            = null;
        int maxLineItemNo                   = 0;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn  	= DataSource.getConnection();
            }
            pstmt 	= conn.prepareStatement( "SELECT MAX(LINEITEM_NO) AS MAX_LINEITEM_NO FROM PURCHASEORDER_LINE_ITEM_TABLE WHERE PURCHASEORDER_ID = ?" );
            pstmt.setInt( 1, poId );
            rset 		= pstmt.executeQuery();
            if( rset.next() )
            {
                maxLineItemNo = rset.getInt ( "MAX_LINEITEM_NO" );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Purchase Order Receipt Lot details from database", sqlex );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); rset = null; } catch (SQLException excp) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); pstmt = null; } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); conn = null; } catch (SQLException excp) {}
            }
        }
        return maxLineItemNo;
    }
    /*
	 * Returns all the Shipping Address Contact Persons
	 */
	public ArrayList loadVendorSalesAddresses(int vendor_id) throws ScalarException
	{
		ArrayList salesAddressList = new ArrayList();
		Connection conn         = null;
		CallableStatement cstmt = null;
		ResultSet rs            = null;
		Address address 		= null;

		try
		{

			conn  	= DataSource.getConnection( Constants.DATASOURCE_TYPE_LOCAL );
			cstmt 	= conn.prepareCall( "{ call sp_load_vendor_sales_addresses( ? ) }" );
			cstmt.setInt( 1, vendor_id );
			rs 		= cstmt.executeQuery();

			while( rs.next() )
			{
				address = new Address();
				address.setAddressId( rs.getInt( "Address_ID" ) );
				address.setAddress1( rs.getString( "Address_1" ) );
				address.setAddress2( rs.getString( "Address_2" ) );
				address.setCity( rs.getString( "City" ) );
				address.setState( rs.getString( "State" ) );
				address.setZipCode( rs.getString( "Zipcode" ) );
				address.setCountry( rs.getString( "Country" ) );
				address.setPhoneNumber( rs.getString( "Phone_Number" ) );
				address.setFaxNumber( rs.getString( "Fax_Number" ) );
				address.setEmail( rs.getString( "Email" ) );
				address.setContactPerson( rs.getString( "Contact_Person" ) );
				address.setCellNumber( rs.getString( "Cell_Number" ) );
				address.setAddress3( rs.getString( "Address_3" ) );
				address.setAddress4( rs.getString( "Address_4" ) );
				address.setDefaultAddress( rs.getString( "is_Default" ) );
				address.setContactPersonAddressId( rs.getInt( "ContactPersonAddress_Id" ) );
				salesAddressList.add( address );
			}
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new ScalarException( "Could not Load Purchase Order Sales Address", e );
		}
		finally
		{
			if ( rs != null )
			{
				try
				{
					rs.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the result set in Load Purchase Order Sales Address" );
				}
			}
			if ( cstmt != null )
			{
				try
				{
					cstmt.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the statement in Load Purchase Order Sales Address" );
				}
			}
			if ( conn != null )
			{
				try
				{
					conn.close();
				}
				catch ( Throwable th )
				{
					Logger.error( "Error while closing the connection in Load Purchase Order Sales Address" );
				}
			}
		}
		return salesAddressList;
	}
}
